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
);