SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
Cascade will work when you delete something on table Courses
. Any record on table BookCourses
that has reference to table Courses
will be deleted automatically.
But when you try to delete on table BookCourses
only the table itself is affected and not on the Courses
follow-up question: why do you have CourseID
on table Category?
Maybe you should restructure your schema into this,
CREATE TABLE Categories
(
Code CHAR(4) NOT NULL PRIMARY KEY,
CategoryName VARCHAR(63) NOT NULL UNIQUE
);
CREATE TABLE Courses
(
CourseID INT NOT NULL PRIMARY KEY,
BookID INT NOT NULL,
CatCode CHAR(4) NOT NULL,
CourseNum CHAR(3) NOT NULL,
CourseSec CHAR(1) NOT NULL,
);
ALTER TABLE Courses
ADD FOREIGN KEY (CatCode)
REFERENCES Categories(Code)
ON DELETE CASCADE;
on cascade delete on a table with two FK to the same table
You can't have multiple or circular cascade paths: it becomes ambiguous what you want to do (say one CASCADE NULL and the other CASCADE DELETE)
I'd use a stored procedure to delete from Friends
first in a transaction then from Users
(in a TRY/CATCH of course to deal with errors)
BEGIN TRAN
DELETE Friends WHERE User1ID = @UserID;
DELETE Friends WHERE User2ID = @UserID;
DELETE Users WHERE UserID = @UserID;
COMMIT TRAN
Related Topics
Designing a SQL Schema for a Combination of Many-To-Many Relationship (Variations of Products)
Should Every SQL Server Foreign Key Have a Matching Index
Microsoft T-SQL to Oracle SQL Translation
How to Get a List of Element Names from an Xml Value in SQL Server
Hql: How to Perform an Inner Join on a Subquery
Faster Way to Insert, via Script, in Oracle
What's the Difference Between Charfield and Textfield in Django
How to Delete from a Table Where Id Is in a List of Ids
What Is the Purpose for Using Option(Maxdop 1) in SQL Server
Ora-00972 Identifier Is Too Long Alias Column Name
Base 36 to Base 10 Conversion Using SQL Only
How to Measure the Execution Time of a Query on Spark
How to Group Timestamps into Islands (Based on Arbitrary Gap)
Search an Oracle Database for Tables with Specific Column Names