The foreign key is used to establish a referential constraint between the child table(in which column is defined as foreign key) and parent table (in which foreign key of the child table becomes primary key). For example if we have an ORDER table in which foreign key is defined as TRANSACTION_ID. This foreign key will refer to the TRANSACTION_ID column of TRANSACTIONS table. In this TRANSACTIONS table, TRANSACTION_ID will be the primary key. The parent table here is TRANSACTIONS table while the child table here is ORDERS table.
The CASCADE rule of the foreign key states that when any entry is deleted from the parent table then all the dependent rows in the child table should also get deleted. For example,If there is one entry in the TRANSACTIONS table with TRANSACTION_ID as ‘A2234’(primary key). If this entry is deleted from the TRANSACTIONS table, then all the rows in the ORDERS table having foreign key TRANSACTION_ID as ‘A2234’ should also get deleted. We can set the CASCADE rule as below.
CREATE TABLE ORDERS (ORDER_ID CHAR(15) NOT NULL, ORDER_DATE DATE, ORDER_TOTAL DECIMAL(9,2), TRANSACTION_ID CHAR(15), PRIMARY KEY(ORDER_ID), FOREIGN KEY(TRANSACTION_ID) REFERENCES TRANSACTIONS ON DELETE CASCADE) IN DB4ES01;
The REFERENCES keyword is followed by the name of the parent table and in order to set the CASCADE rule we will use the keyword ON DELETE CASCADE.