ON DELETE CASCADE
I we have a primary key and Foreign key relations are there in the tables we can not delete the data from primary key value. Since that value used in the child tables. But this Possible if we create table with ON DELETE CASCADE option. Example is given below.
CREATE TABLE Items
(Sno INT PRIMARY KEY, [Name] VARCHAR(50))
INSERT INTO Items
VALUES(1,'Pen')
INSERT INTO Items
VALUES(2,'Paper')
INSERT INTO Items
VALUES(3,'Pencil')
INSERT INTO Items
VALUES(4,'Sketches')
SELECT * FROM Items
GO
CREATE TABLE Orders
(
OrderID INT PRIMARY KEY
, Sno INT CONSTRAINT FK_02345 FOREIGN KEY REFERENCES Items(Sno)
, Qty INT
, FOREIGN KEY (Sno) REFERENCES Items ON DELETE CASCADE
)
INSERT INTO Orders
VALUES(2,3,15)
DELETE FROM Items--ParentTable
WHERE Sno=3
SELECT * FROM Orders
CREATE TABLE Items
(Sno INT PRIMARY KEY, [Name] VARCHAR(50))
INSERT INTO Items
VALUES(1,'Pen')
INSERT INTO Items
VALUES(2,'Paper')
INSERT INTO Items
VALUES(3,'Pencil')
INSERT INTO Items
VALUES(4,'Sketches')
SELECT * FROM Items
GO
CREATE TABLE Orders
(
OrderID INT PRIMARY KEY
, Sno INT CONSTRAINT FK_02345 FOREIGN KEY REFERENCES Items(Sno)
, Qty INT
, FOREIGN KEY (Sno) REFERENCES Items ON DELETE CASCADE
)
INSERT INTO Orders
VALUES(2,3,15)
DELETE FROM Items--ParentTable
WHERE Sno=3
SELECT * FROM Orders
Comments