Oracle Foreign Key usage (2)-delete cascade and oraclecascade

Oracle Foreign Key usage (2)-delete cascade and oraclecascade

DELETE CASCADE)

Target

This example shows how to use cascading deletion in Oracle Foreign keys.

What is delete cascade )?

Cascading deletion means that when a record in the primary table (parent table) is deleted, the associated records in the subtable are also deleted automatically.

The cascading deletion of Foreign keys can be defined at the time of TABLE creation or by using the alter table syntax.

When creating a table, define the cascading deletion Syntax:

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(50) not null,  contact_name varchar2(50),  CONSTRAINT pk_supplier PRIMARY KEY (supplier_id));create table tb_products(  product_id number not null,  product_name varchar2(100),  supplier_id number not null,  constraint fk_products_supplier      foreign key (supplier_id)      references tb_supplier(supplier_id)     on delete cascade);

Verification: 1) insert test data into the table

-- Insert into tb_supplier values (1, 'Microsoft ', 'Microsoft') into the master table; insert into tb_supplier values (2, 'ibm ', 'ibm '); insert into tb_supplier values (3, 'linux ', 'linux'); insert into tb_supplier values (4, 'Dell ', 'Dell'); insert into tb_supplier values (5, 'lenovo ', 'lenovo'); insert into tb_supplier values (6, 'Google ', 'Google'); -- insert sample data to tb_products values (1, 'windows', 1); insert into tb_products values (2, 'Office ', 1); insert into tb_products values (3, 'ica ICA', 2 ); insert into tb_products values (4, 'congos ', 2); insert into tb_products values (5, 'ubuntu', 3); insert into tb_products values (6, 'centos ', 3); insert into tb_products values (7, 'initation', 4); insert into tb_products values (8, 'thinkpad ', 5); insert into tb_products values (9, 'yuncp ', 5); insert into tb_products values (10, 'android', 6); insert into tb_products values (11, 'chrome', 6 ); insert into tb_products values (12, 'hadoop ', 6); -- submit commit;

2) Delete the supplier_id = 1 data in the master table, and verify whether the associated data in the sub-table is deleted.

-- Delete primary table data delete from tb_supplier where supplier_id = 1; -- submit commit; -- verify whether the Sub-table data is deleted select * from tb_products;

3) from the above results, we can see that the associated foreign key records have been cascading deleted.

Use alter table syntax to define cascading deletion Syntax:

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) recreate the previous sample table

-- Create table tb_supplier (supplier_id number not null, supplier_name varchar2 (50) not null, contact_name varchar2 (50), CONSTRAINT pk_supplier primary key (supplier_id )); create table tb_products (product_id number not null, product_name varchar2 (100), 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 into tb_supplier values (1, 'Microsoft ', 'Microsoft') into the master table; insert into tb_supplier values (2, 'ibm ', 'ibm '); insert into tb_supplier values (3, 'linux ', 'linux'); insert into tb_supplier values (4, 'Dell ', 'Dell'); insert into tb_supplier values (5, 'lenovo ', 'lenovo'); insert into tb_supplier values (6, 'Google ', 'Google'); -- insert sample data to tb_products values (1, 'windows', 1); insert into tb_products values (2, 'Office ', 1); insert into tb_products values (3, 'ica ICA', 2 ); insert into tb_products values (4, 'congos ', 2); insert into tb_products values (5, 'ubuntu', 3); insert into tb_products values (6, 'centos ', 3); insert into tb_products values (7, 'initation', 4); insert into tb_products values (8, 'thinkpad ', 5); insert into tb_products values (9, 'yuncp ', 5); insert into tb_products values (10, 'android', 6); insert into tb_products values (11, 'chrome', 6 ); insert into tb_products values (12, 'hadoop ', 6); -- submit commit;

5) test cascade Deletion

-- Delete primary table data delete from tb_supplier where supplier_id = 1; -- submit commit;

6) Verification:

-- Verify whether the Sub-table data is deleted. select * from tb_products;

7) from the above results, we can see that the associated foreign key records have been cascading deleted.

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!
Bytes --------------------------------------------------------------------------------------------------------------------

ORACLE: How can I use an SQL statement to change the ON DELTE CASCADE constraint to ON DELETE SET NULL when a foreign key already exists?

Delete the foreign key with the original cascading deletion and recreate the foreign key with No Delete Set Null.
For example:
Alter table Emp drop constraint FK_Emp;
Alter table Emp add constraint FK_Emp foreign key (DEPTNO)
References Dept (DEPTNO) on delete set null;

Cascading deletion of tables in oracle databases

You are advised to select method 1 in either of the two ways, which is simple and convenient.

Method 1: trigger solution (the following code can be used directly for copy without modification)
Create or replace trigger delete_dept
Before delete on DEPT
For each row
Begin
Delete from EMP where DEPT_NO =: old. DEPT_NO;
Delete from POS where DEPT_NO =: old. DEPT_NO;
End;
/

Method 2: Modify your foreign key settings to achieve cascading deletion. The specific implementation is as follows:
A) first query the names of foreign keys in the EMP and POS tables (if you know the foreign key name, this step can be omitted)
Select CONSTRAINT_NAME, TABLE_NAME from user_constraints where CONSTRAINT_TYPE = 'r' and TABLE_NAME in ('emp', 'pos ');

B) Delete the Foreign keys in the EMP and POS tables, and re-establish the foreign key mode that allows cascading deletion.
Alter table EMP drop constraint foreign key name;
Alter table POS drop constraint foreign key name;
Alter table EMP add constraint foreign key name foreign key (DEPT_NO) references DEPT (DEPT_NO) on delete cascade;
Alter table POS add constraint foreign key name foreign key (DEPT_NO) references DEPT (DEPT_NO) on delete cascade;

---
The above is helpful to you.