Oracle External key (Foreign key) usage details (ii)-Cascade Delete (delete CASCADE)

Oracle FOREIGN KEY (Foreign key) cascade Delete (delete CASCADE)

Target

Examples of how to use cascading deletions in Oracle foreign keys

What is cascading delete (DELETECASCADE)?

Cascade Delete is when a record in the primary table (parent table) is deleted, and the associated record in the child table is automatically deleted.

cascading deletions for foreign keys can be defined when a table is created, or by using the ALTER TABLE syntax.

To define cascading deletions when creating a tableSyntax:

CREATE TABLE table_name (    column1 datatype null/not null,    column2 datatype null/not null,    ...    CONSTRAINT fk_column          FOREIGN KEY (column1,column2,... column_n)          REFERENCES parent_table (column1, Column2,... Column_n) on          DELETE casecade);

Example:

CREATE TABLE Tb_supplier (  supplier_id number not NULL,  supplier_name varchar2 () is not NULL,  Contact_Name VARCHAR2 (+),  CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)); Create Table tb_products (  product_id number Not NULL,  product_name varchar2 (+),  supplier_id number NOT NULL,  constraint Fk_products_supplier      Foreign key (supplier_id)      references Tb_supplier (supplier_id) on     delete cascade);

Verification:1) inserting test data into the table

--Insert the Main Table sample data insert into Tb_supplier values (1, ' Microsoft ', ' Microsoft ') and insert into tb_supplier values (2, ' IBM ', ' IBM '); INSERT into Tb_supplier values (3, ' Linux ', ' Linux ') and insert into Tb_supplier values (4, ' dell ', ' dell '); INSERT INTO tb_ Supplier values (5, ' Lenovo ', ' Lenovo '), insert into tb_supplier values (6, ' Google ', ' Google ');--Child table Insert sample data insert INTO Tb_ Products VALUES (1, ' windows ', 1); INSERT into tb_products values (2, ' Office ', 1); INSERT into tb_products values (3, ' Informatica ', 2); INSERT into tb_products values (4, ' Congos ', 2); Insert to Tb_products values (5, ' Ubuntu ', 3); INSERT INTO Tb_products VALUES (6, ' CentOS ', 3), insert into tb_products values (7, ' inspiration ', 4); INSERT into tb_products values (8, ' ThinkPad ', 5); INSERT into tb_products values (9, ' y410p ', 5); INSERT into tb_products values ("Android", 6); INSERT INTO TB _products values (one, ' Chrome ', 6), insert into tb_products values (' Hadoop ', 6);--commit commits;

2) Delete the data from the primary table supplier_id=1 and verify that the associated data in the child table is deleted

--Delete Main Table data delete from Tb_supplier where supplier_id=1;--commit commit;--Verify that the child table data is deleted select * from Tb_products;

3) from the above results, it can be seen that the associated foreign key record has been cascade deleted.

To define a cascade delete using the ALTER TABLE syntaxSyntax:

ALTER TABLE table_nameadd CONSTRAINT constraint_name      FOREIGN KEY (column1, Column2,... column_n)      REFERENCES Parent_table (Column1, Column2, ... column_n) on      DELETE CASCADE;

Example:1) Delete the previous sample table

--Delete the previous Sample Table drop table Tb_products;drop table tb_supplier;

2) Sample table before rebuilding

--Rebuilding the Sample table CREATE table Tb_supplier (supplier_id number not  null,  supplier_name varchar2 () is not NULL, the contact  _name varchar2 (),  CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)); Create Table tb_products (  product_id Number NOT NULL,  product_name varchar2 (+),  supplier_id number not NULL);

3) Add a foreign key to the table

ALTER TABLE TB_PRODUCTSADD constraint fk_products_supplier    foreign key (supplier_id)    references Tb_supplier ( supplier_id) on    delete cascade;

4) Insert sample Data

--Insert the Main Table sample data insert into Tb_supplier values (1, ' Microsoft ', ' Microsoft ') and insert into tb_supplier values (2, ' IBM ', ' IBM '); INSERT into Tb_supplier values (3, ' Linux ', ' Linux ') and insert into Tb_supplier values (4, ' dell ', ' dell '); INSERT INTO tb_ Supplier values (5, ' Lenovo ', ' Lenovo '), insert into tb_supplier values (6, ' Google ', ' Google ');--Child table Insert sample data insert INTO Tb_ Products VALUES (1, ' windows ', 1); INSERT into tb_products values (2, ' Office ', 1); INSERT into tb_products values (3, ' Informatica ', 2); INSERT into tb_products values (4, ' Congos ', 2); Insert to Tb_products values (5, ' Ubuntu ', 3); INSERT INTO Tb_products VALUES (6, ' CentOS ', 3), insert into tb_products values (7, ' inspiration ', 4); INSERT into tb_products values (8, ' ThinkPad ', 5); INSERT into tb_products values (9, ' y410p ', 5); INSERT into tb_products values ("Android", 6); INSERT INTO TB _products values (one, ' Chrome ', 6), insert into tb_products values (' Hadoop ', 6);--commit commits;

5) Test Cascade Delete

--Delete Main Table data delete from Tb_supplier where supplier_id=1;--commit commit;

6) Verify:

--Verify that the child table data is deleted select * from Tb_products;

7) from the above results, it can be seen that the associated foreign key record has been cascade deleted.

--------------------------------------------------------------------------------------------------------------- ---------------------------------------

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source!
----------------------------------------------------------------------------------------------------------- ---------

Oracle External key (Foreign key) usage details (ii)-Cascade Delete (delete CASCADE)