Last week I was in Belgrade Serbia delivering a seminar and an attendee reminded me of something I knew once but had totally forgotten about. It had to do with foreign keys and the dreaded NULL value.
Many of you might think the following to be not possible, we'll start with the tables:
ops$tkyte%ORA11GR2> create table p
2 ( x int,
3 y int,
4 z int,
5 constraint p_pk primary key(x,y)
6 )
7 /
Table created.
ops$tkyte%ORA11GR2> create table c
2 ( x int,
3 y int,
4 z int,
5 constraint c_fk_p foreign key (x,y) references p(x,y)
6 )
7 /
Table created.
Looks like a normal parent child relationship - a row may exist in C if and only if a parent row exists in P. If that is true - then how can this happen:
ops$tkyte%ORA11GR2> select count( x||y ) from p;
COUNT(X||Y)
-----------
0
ops$tkyte%ORA11GR2> select count( x||y ) from c;
COUNT(X||Y)
-----------
1
There are zero records in P - none. There is at least one record in C and that record has a non-null foreign key. What is happening?
It has to do with NULLs and foreign keys and the default "MATCH NONE" rule in place. If your foreign key allows NULLs and your foreign key is a composite key - then you must be careful of the condition where by only SOME of the foreign key attributes are not null. For example - to achieve the above magic, I inserted:
ops$tkyte%ORA11GR2> insert into c values ( 1, null, 0 );
1 row created.
The database cannot validate a foreign key when it is partially null. In order to enforce the "MATCH FULL" option of a foreign key - you would want to add a constraint to your table:
ops$tkyte%ORA11GR2> alter table c add constraint check_nullness
2 check ( ( x is not null and y is not null ) or
3 ( x is null and y is null ) )
4 /
Table altered.
That will ensure either:
- All of the columns are NULL in the foreign key
- 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 work.
DIGITAL JUICE
No comments:
Post a Comment
Thank's!