The following example shows how to setup and cascade works when deleting record from parent table.
1) Shows how DELETE cascade works
— Parent table
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
— children table with foreign key
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE
);
Records inserted in the table:
insert into supplier values ( 1, ‘supplier 1’, ‘contact name 1’ );
insert into products values ( 1, 1 );
— shows when record is deleted from parent table it deletes from the table that has foreign key due to “ON DELETE CASCADE”
SQL> delete from supplier where supplier_id = 1;
1 row deleted.
— record was deleted from products table
SQL> select * from products;
no rows selected
SQL> select * from supplier;
no rows selected
SQL> commit;
Commit complete.
2) Shows how DELETE cascade SET NULL works, it sets foreign key to NULL when primary key is deleted
— Parent table
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
— children table with foreign key
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE SET NULL
);
Records inserted in the table:
insert into supplier values ( 1, ‘supplier 1’, ‘contact name 1’ );
insert into products values ( 1, 1 );
— shows when record is deleted from parent table it sets NULL values to foreign keydue to “ON DELETE SET NULL”
SQL> delete from supplier where supplier_id = 1;
1 row deleted.
SQL> select * from supplier;
no rows selected
— SUPPLIER_ID is set to NULL when the primary key record is deleted
SQL> select * from products;
PRODUCT_ID SUPPLIER_ID
———- ———–
1
SQL> commit;
Commit complete.
3) The following example shows if a table doesn’t have ON DELETE CASCADE OR INITIALLY DEFERRED
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
);
Records inserted in the table:
insert into supplier values ( 1, ‘supplier 1’, ‘contact name 1’ );
insert into products values ( 1, 1 );
— when record from parent is being deleted due to foreign key constraint it can’t be deleted till all children are deleted
SQL> delete from supplier where supplier_id = 1;
delete from supplier where supplier_id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_SUPPLIER) violated – child record
found
— If you don’t have ON DELETE CASECADE set on the foreign key, it can be done as follows record from tables that have foreign key is deleted first then the record from parent can be deleted.
SQL> delete from products where supplier_id = 1;
1 row deleted.
SQL> delete from supplier where supplier_id = 1;
1 row deleted.
SQL> commit;
Commit complete.
4)In case when one wants updating the primary key and cascade the change to foreign key, this can be done through a trigger.
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10),
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE SET NULL
);
— The following trigger updates records that are foreign key
create or replace trigger cascade_supplier_update
after update of supplier_id on supplier
for each row
begin
update products
set supplier_id = :new.supplier_id
where supplier_id = :old.supplier_id;
end;
/
Records inserted in the table:
insert into supplier values ( 1, ‘supplier 1’, ‘contact name 1’ );
insert into products values ( 1, 1 );
— Following test shows updating the parent table, the trigger updates the dependent table and Oracle doesn’t throw exception
SQL> update supplier set supplier_id = 2 where supplier_id = 1;
1 row updated.
— shows supplier_id in products table was updated
SQL> select * FROM products;
PRODUCT_ID SUPPLIER_ID
———- ———–
1 2
5)In case when one wants updating the primary key using “INITIALLY DEFERRED”, updating the primary key Oracle will check constraint when commit is executed and check constraints during COMMIT
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10),
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE SET NULL INITIALLY DEFERRED
);
Records inserted in the table:
insert into supplier values ( 1, ‘supplier 1’, ‘contact name 1’ );
insert into products values ( 1, 1 );
— Shows when UPDATE primary key oracle will allow the UPDATE to occur on the parent table and then one can UPDATE the foreign key
SQL> update supplier set supplier_id = 2 where supplier_id = 1;
1 row updated.
SQL> update products set supplier_id = 2 where supplier_id = 1;
1 row updated.
SQL> commit;
Commit complete.
— If one were to update the PRIMARY key but not the foreign key and COMMIT, Oracle will generate “ORA-02292” error and the transaction will be rolled back
SQL> update supplier set supplier_id = 2 where supplier_id = 1;
1 row updated.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SCOTT.FK_SUPPLIER) violated – child record found
alexlima.com 