SQL & PL/SQL

Hello All,

When composite foreign key is used in a schema. Oracle doesn't check necessary rules properly as shown below. 

create table parent_tab
(
x int,
y int,
z int,
constraint p_key primary key (x, y)
);
create table child_tab
(
x int,
y int,
z int,
constraint f_key foreign key (x, y) references parent_tab(x, y)
);

insert into child_tab (x, y, z) values (1, NULL, 4);
commit;

select count(x||y) from parent_tab;
0

select count(x||y) from child_tab;
1

Tom Kyte says the following for the explanation.

The database cannot validate a foreign key when it is partially null. In order to enforce the MATCH FULL rule for composite foreign keys, you would add a constraint to your table:
  
The constraint will ensure that either

All of the columns are NULL in the foreign key, or
None of the columns are NULL in the foreign key


As long as that constraint is in place, your foreign key will work as you probably think it should.

However, he doesn't explain WHY? I mean why Oracle cannot validate the foreign key when it is partially NULL?

Regards