The Previous tutorial was all about foreign key constraint in Oracle Database. There we learnt that the foreign key constraint establishes a link / relation between PARENT and CHILD table.
The foreign key is defined in the child table and the parent table contains the reference column. However because of this link we cannot update or delete the rows of the parent table.
Go To my previous tutorial on foreign key and create parent (authors) and child (books) table with foreign key and after creating them try to drop the parent (authors) table and see what will happen.
(To drop parent table AUTHORS execute DROP TABLE AUTHORS; ddl)
So, what is special about foreign key which won’t let us Delete or Update the records of the parent table?
When you define a simple foreign key, the Oracle engine is by default set to ON DELETE NO ACTION clause. This means that you are allowed to update the rows in the parent table however you cannot delete rows from the parent table. This default behavior is called Restrict rule. This rule doesn’t allow users to delete or update reference data in the parent table.
Does that mean we cannot update or delete the records of parent table if we have a foreign key constraint defined on it?
Definitely not. You can easily override this restrict rule and change the default behavior of the foreign key either to SET NULL or to DELETE CASCADE.
That’s the beauty of working with Oracle Database, you always get the solutions to the problems.
So how can we change the default behavior of foreign key?
In Oracle Database you get two referential actions to override the restrict rule and change the default behavior of foreign key. These two referential actions are –
- ON DELETE SET NULL and
- ON DELETE CASCADE
In this tutorial we will concentrate on the first referential action which is On Delete Set Null and see how we can change the default behavior which is ON DELETE NO ACTION of the foreign key in Oracle Database.
What is ON DELETE SET NULL clause of the foreign key constraint?
On Delete Set Null clause sets all the records of the column which is defined as a foreign key in the child table to Null if the corresponding record in the parent table is deleted.
Can you demonstrate us how to define an “On Delete Set Null” clause with foreign key?
Sure, why not let’s do an example demonstrating the On Delete Set Null clause with foreign key in Oracle Database. For the demonstration we will be using the same tables which we created in the previous tutorial.
Let’s first create the parent table which will hold the reference column of our foreign key. The name of the table will be Author and there will be two columns in this table author_id and author_name. The first column which is author_id will serve as the reference column for the foreign key.
CREATE TABLE author
( author_id NUMBER(3) CONSTRAINT athr_aid_pk PRIMARY KEY, author_name VARCHAR2(30) );
We specify the clause On Delete Set Null along with the definition of foreign key and as mentioned above, we define a foreign key in the child table. Thus now we need a child table.
CREATE TABLE books
book_author_id NUMBER(3) CONSTRAINT bok_ai_fk REFERENCES author(author_id) ON DELETE SET NULL
Here we have our child table. In this table the column book_author_id will serve as the foreign key. If you will see the foreign key definition of this column then you will notice that at the end of the foreign key definition we specified our clause which is “On Delete Set Null”.
This is how you define a foreign key with ON DELETE SET NULL with create table in column level.
Is there any way to confirm that the foreign key is defined with on delete set null clause this time?
Yes, you can check this constraint by executing the query on – USER_CONSTRAINTS data dictionary.
WHERE table_name = ‘BOOKS’ ;
So guys, this is the process by which you can change Oracle’s default behavior to ON DELETE SET NULL clause using foreign key. Hope you find this blog useful.
Do read my next article explaining ON DELETE CASCADE clause. You can also watch my YouTube tutorial on the same. Please do like, share and subscribe! Thanks & have a great day!