SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

by

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;

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. How to create multiple one to one’s
  3. What are best practices for multi-language database design? [closed]
  4. Strings as Primary Keys in SQL Database [closed]
  5. Are soft deletes a good idea? [duplicate]
  6. Database Design for Revisions?
  7. A beginner’s guide to SQL database design [closed]
  8. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  9. Storing money in a decimal column – what precision and scale? [closed]
  10. single fixed table with multiple columns vs flexible abstract tables
  11. Primary key or Unique index?
  12. How to design a database for User Defined Fields?
  13. Composite Primary key vs additional “ID” column?
  14. Database EAV Pros/Cons and Alternatives
  15. Max length for client ip address [duplicate]
  16. Name database design notation you prefer and why?
  17. MySQL – how to front pad zip code with “0”?
  18. Use email address as primary key?
  19. Composite Primary Keys : Good or Bad?
  20. SQL: Using NULL values vs. default values
  21. What does ON [PRIMARY] mean?
  22. What is the best way to implement soft deletion?
  23. Relational Data Model for Double-Entry Accounting
  24. Temporal database design, with a twist (live vs draft rows)
  25. Historical / auditable database
  26. Single or multiple databases
  27. How does database indexing work? [closed]
  28. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  29. Using a database table as a queue
  30. Executing SQL query on multiple databases

Leave a Comment