What is Cascading in SQL SERVER PART 8 With Example Code

ON DELETE NO ACTION: This means that if you attempt to delete a record in the parent table (NewAuthors) that has corresponding records in the child table (NewBooks), an error will be raised, and the delete operation will be rolled back. This ensures that no action is taken that would violate referential integrity.

ON DELETE CASCADE: With this option, if a record in the parent table is deleted, all corresponding records in the child table will also be deleted automatically. This ensures that there are no orphaned records in the child table.

ON DELETE SET DEFAULT: When you delete a record in the parent table, the corresponding foreign key values in the child table are set to their default values (in this case, 0). This can help maintain referential integrity by providing a default value rather than leaving the field empty or null.



-- Create the NewAuthors table
CREATE TABLE NewAuthors (
    NewAuthorId INT PRIMARY KEY,
    NewAuthorName VARCHAR(255)
);
 
-- Create the NewBooks table with a foreign key referencing NewAuthors
CREATE TABLE NewBooks (
    NewBookId INT PRIMARY KEY,
    NewBookTitle VARCHAR(255),
    NewAuthorId INT,
    FOREIGN KEY (NewAuthorId) REFERENCES NewAuthors(NewAuthorId) ON DELETE NO ACTION
);
 
 
-- Create the NewBooks table with a foreign key referencing NewAuthors
CREATE TABLE NewBooks (
    NewBookId INT PRIMARY KEY,
    NewBookTitle VARCHAR(255),
    NewAuthorId INT,
    FOREIGN KEY (NewAuthorId) REFERENCES NewAuthors(NewAuthorId) ON UPDATE NO ACTION ON DELETE CASCADE
);
 
 
-- Create the NewBooks table with a foreign key referencing NewAuthors
CREATE TABLE NewBooks (
    NewBookId INT PRIMARY KEY,
    NewBookTitle VARCHAR(255),
    NewAuthorId INT DEFAULT 0, -- Default value for NewAuthorId
    FOREIGN KEY (NewAuthorId) REFERENCES NewAuthors(NewAuthorId) ON DELETE SET DEFAULT
);