Tom Kyte escribe: When is a foreign key not a foreign key...
I learn or relearn something new every day about Oracle. Just about every day really!
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.
See also: http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_constraints.htm#ADFNS273
Me pareció profundamente curioso este descubrimiento de Tom Kyte, en la base de datos e investigué, si en los nuevas revisiones ( releases ), el problema aún existía. Para ello, utilicé una base de datos 11gR2 11.2.0.2 con Oracle Linux 5.6
En las líneas a continuación, podrán verificar que el problema inicial del conteo sobre el número de registros en las tablas, a la hora de hacer la concatenación de los campos que forman parte del FK referenciado hacia la llave primaria en la primera tabla, ya no existe en el release 11.2.0.2 de la base de datos.
Sin embargo, la ausencia de la validación de nulos en la tabla hijo, hace posible insertar registros en dicha tabla, sin validar, si el "padre" existe. Por tanto, si estamos en presencia de una relación de Padre-Hijo, a la hora de definición de un FK, es necesario adicionar la validación que Tom hace al final de su nota, para evitar una sorpresa más adelante.
Si usted tiene algunos "bugs" interesantes como éste y no ha obtenido respuesta en forums o en MOS, les invitamos a que nos escriba a mi cuenta de correo ( ronald.vargas.quesada@gmail.com ) e intentaremos ayudarle con su problema.
[oracle@svrlnxdborcl01 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 23 21:41:55 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> create user demo identified by demo;
User created.
SQL> grant create session to demo;
Grant succeeded.
SQL> alter user demo quota unlimited on users;
User altered.
SQL> grant create any table to demo;
Grant succeeded.
SQL> connect demo/demo
Connected.
SQL> create table p
2 ( b int,
3 c int,
4 d int, constraint pk_p primary key(b,c));
Table created.
SQL> create table q
2 ( b int,
3 c int,
4 d int, constraint q_fk_p foreign key (b,c) references p(b,c));
Table created.
SQL> select count(b||c) from p;
COUNT(B||C)
-----------
0
SQL> select count(b||c) from q;
COUNT(B||C)
-----------
0
SQL> desc p
Name Null? Type
------------------ -------- ----------
B NOT NULL NUMBER(38)
C NOT NULL NUMBER(38)
D NUMBER(38)
SQL> desc q
Name Null? Type
------------------ -------- ----------
B NUMBER(38)
C NUMBER(38)
D NUMBER(38)
SQL> insert into q values(1, null, 0);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(b||c) from p;
COUNT(B||C)
-----------
0
SQL> select count(b||c) from q;
COUNT(B||C)
-----------
1
SQL> truncate table q;
Table truncated.
SQL> alter table q add constraint check_nullness
2 check (( b is not null and c is not null ) or
3 (b is null and b is null ));
Table altered.
SQL> insert into q values(1, null, 0);
insert into q values(1, null, 0)
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.CHECK_NULLNESS) violated
SQL>
