Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
How to transport a tablespace in Oracle 10g Oracle 10g db_nk_cache_size Parameters

Handling Nulls in a Primary and Foreign key relationship

Posted by Zahid on August 13, 2009.

As you all may know that foreign key enforces the data integrity of relevant data in a parent and child table in a way that no record can be deleted from the parent table if a corresponding record exists in the child and no record can be created in the child table if no corresponding record exists in the parent. Please see below:

SQL> create table p1 ( name varchar2(80) , 2 class_sys varchar2(80), 3 parent_s varchar2(19) 4 ); Table created. SQL> alter table p1 2 add constraint uk_p1 unique (name,class_sys,parent_s); Table altered. SQL> create table c1 ( id number , 2 c_name varchar2(80), 3 c_sys varchar2(80), 4 parent_cl_s varchar2(19) 5 ); Table created. SQL> alter table c1 2 add constraint fk_c1 foreign key (c_name,c_sys,parent_cl_s) 3 references p1 (name, class_sys,parent_s); Table altered. SQL> insert into c1 VALUES (1,'zk','zk','zk'); insert into c1 VALUES (1,'zk','zk','zk') * ERROR at line 1: ORA-02291: integrity constraint (SYSTEM.FK_C1) violated - parent key not found SQL> insert into p1 values ('zk','zk','zk'); 1 row created. SQL> insert into c1 VALUES (1,'zk','zk','zk'); 1 row created. SQL> commit; Commit complete. SQL> delete from p1; delete from p1 * ERROR at line 1: ORA-02292: integrity constraint (SYSTEM.FK_C1) violated - child record found

As you can see above when I tried creating a record in table c1 (child table) it was bounced back saying that a parent was not found. But when I created a parent record in table p1 (parent table) then I was able to create a record in c1 as well. I was also not able to delete the record from the table p1 while there was a child record in c1. Now that clearly demonstrate the behavior of a Foreign Key. Today we discovered a bug in the client application which was duly reported to me being a DBA, and it was about a successful deletion of a parent record even the child exist. Please see below:

SQL> delete from c1; 1 row deleted. SQL> delete from p1; 1 row deleted. SQL> commit; Commit complete. SQL> insert into p1 values ('zk','zk',null); 1 row created. SQL> insert into c1 VALUES (1,'zk','zk',null); 1 row created. SQL> commit; Commit complete. SQL> delete from p1; 1 row deleted. /* Note that the record from p1 is successfully deleted leaving an orphan record in c1. */ SQL> roll; Rollback complete. /* Rollback the incorrect delete. Now we again have one record in parent and one in child. */ SQL> insert into c1 VALUES (1,'zk','zkq',null); 1 row created. SQL> commit; Commit complete. /* This time I tried creating another record in c1 with a combination of values ('zk','zkq',null) in the composite foreign key columns, which don't exist in the parent at all. */ SQL>

This is happening because of NULLS in the child or parent columns. In fact foreign key doesn't enforce parent child relationship at all when there is null involved in parent or child column, just like you may have nulls in a child column referencing to a primary key which can never has a null in it.

Most of you may know this already, but those who never noticed this before, consider it when you are in designing phase of your application or may be its worth having look at already running applications to prevent getting surprisingly hit by this unusual behavior.