USES OF ON DELETE CASCADE and ON DELETE SET NULL - DBMS

 

DBMS CLASS

26.10.2021

CREATE TABLE:

CREATE TABLE Cust
	(cid VARCHAR2(5),name VARCHAR2(30),
	age NUMBER(3),city VARCHAR2(5),
	PRIMARY KEY (CID) );

 

INSERT TABLE:

INSERT INTO Cust VALUES ('C1', 'RAHUL', 30, 'KOL');
INSERT INTO Cust VALUES ('C5', 'SMITH', 28, 'KOL');
SELECT * FROM Cust;

 

CREATE TABLE Product USING ON DELETE CASCADE:

CREATE TABLE Product
	(oid VARCHAR2(5),pid VARCHAR2(5),
	p_name VARCHAR2(30),qty NUMBER(3),
	cid VARCHAR(5), PRIMARY KEY (oid),
	FOREIGN KEY (cid) REFERENCES Cust (cid) 
	ON DELETE CASCADE);

 

INSERT TABLE PRODUCT:

INSERT INTO Product VALUES ('O1', 'P9', 'AC', 2,'C5');
INSERT INTO Product VALUES ('O2', 'P2', 'MOBILE', 1,'C5');
INSERT INTO Product VALUES ('O6', 'P9', 'AC', 1,'C1');

 

PRODUCT TABLE:

PRINT SOME ATTRIBUTE 

OF TABLE:

SELECT Cust.cid, name, city, Product.cid, oid, pid, p_name
FROM Cust, Product
WHERE Cust.cid = Product.cid;

 

DELETE 'C5' CUSTOMER DETAILS:

DELETE FROM Cust
WHERE cid = 'C5';

 

AFTER DOING THIS OPERATION(DELETE 'C5' CUSTOMER):

PRODUCT TABLE:

CUST TABLE:

CREATE TABLE Product USING ON DELETE SET NULL:

CREATE TABLE Product
	(oid VARCHAR2(5),pid VARCHAR2(5),
	p_name VARCHAR2(30),qty NUMBER(3),
	cid VARCHAR(5), PRIMARY KEY (oid),
	FOREIGN KEY (cid) REFERENCES Cust (cid) 
	ON DELETE SET NULL);

 

USING ON DELETE SET NULL, IT WILL CREATE TABLE AS USUAL.

DELETE FROM Cust
WHERE cid = 'C5';

 AFTER DELETING CID='C5', CUST TABLE'S C5 ROW WILL BE DELETED COMPLETELY BUT ONLY CHILD TABLE I.E PRODUCT TABLE'S CID COLUMN'S PARTICULAR DATA(I.E. C5) WILL REPLACE BY NULL.

  PRODUCT TABLE:

CUST TABLE: