This discussion is archived
2 Replies Latest reply: Apr 30, 2009 11:23 AM by Aman.... RSS

About Foreign Key constraint

390981 Explorer
Currently Being Moderated
Hi all,

I'm using Oracle 9.2.0.8 and I have a problem with FK constraints
compost with two columns. So, follows an example:
create table t1
(
       id1 number,
       id2 number ,
       no varchar2(30) not null
);

ALTER TABLE t1
      ADD CONSTRAINT PK_t1 PRIMARY KEY (id1, id2);

----

create table t2
(
       code number primary key,
       id1 number, -- fk
       id2 number-- fk
);

alter table t2
      ADD CONSTRAINT FK_t2_vs_t1 FOREIGN KEY (id1, id2)
      REFERENCES t1 (id1, id2);


-- inserts:

insert into t1 values (1, 1, '11');
insert into t1 values (1, 2, '12');
insert into t1 values (1, 3, '13');

insert into t1 values (2, 1, '21');
insert into t1 values (2, 2, '22');
insert into t1 values (2, 3, '23');

----------

insert into t2 values (100, 1, 1); -- ok
insert into t2 values (101, 2, 1); -- ok

insert into t2 values (102, 2, 4); -- foreign key OK. Got oracle error.

       ORA-02291: integrity constraint (SIGTAD1.FK_T2_VS_T1) violated - parent key not found

insert into t2 values (102, null, 4); -- invalid. Didn't check the FK constraint, but I can insert this line. It's wrong.

insert into t2 values (103, 99, null); -- invalid, but insert into table. It's wrong too.


select * from t1;

       ID1        ID2 NO
---------- ---------- --------------
         1          1 11
         1          2 12
         1          3 13
         2          1 21
         2          2 22
         2          3 23

select * from t2;

      CODE        ID1        ID2
---------- ---------- ----------
       100          1          1
       101          2          1
       102                     4
       103         99


select table_name, constraint_name, status,
DEFERRABLE, DEFERRED, VALIDATED, INVALID
from user_constraints
where table_name = 'T2'

TABLE_NAME                     CONSTRAINT_NAME                STATUS   DEFERRABLE     DEFERRED  VALIDATED     INVALID
------------------------------ ------------------------------ -------- -------------- --------- ------------- -------
T2                             SYS_C0017798                   ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED
T2                             FK_T2_VS_T1                    ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED



col column_name format a30
select CONSTRAINT_NAME, column_name, position
from user_cons_columns
where table_name = 'T2'
order by 1, 3;

CONSTRAINT_NAME                COLUMN_NAME                      POSITION
------------------------------ ------------------------------ ----------
FK_T2_VS_T1                    ID1                                     1
FK_T2_VS_T1                    ID2                                     2
SYS_C0017798                   CODE                                    1
So, the FK constraint with compost key only validate with the all columns?
is this the correct on Oracle?

Thank U very much!!!!
  • 1. Re: About Foreign Key constraint
    390981 Explorer
    Currently Being Moderated
    Hi,

    I can create a check constraint like this to solve the problem:
    alter table t2
    add constraint ck_t2_vs_t1 check 
    (((id1 is not null) and (id2 is not null)) or ((id1 is null) and (id2 is null)));
    but I don't agree with this.
  • 2. Re: About Foreign Key constraint
    Aman.... Oracle ACE
    Currently Being Moderated
    Well, if I have understood you correctly, you are not happy seeing null being inserted and accepted in the Foriegn key. This is the correct behavior,
    SQL> select * from V$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    SQL> create table pk ( a number primary key);
    
    Table created.
    
    SQL> create table fk ( a number references pk(a));
    
    Table created.
    
    SQL> insert into pk values(1);
    
    1 row created.
    
    SQL> insert into pk values(2);
    
    1 row created.
    
    SQL> select * from pk;
    
             A
    ----------
             1
             2
    
    SQL> commit;
    
    Commit complete.
    
    SQL> insert into fk values(1);
    
    1 row created.
    
    SQL> insert into fk values(3);
    insert into fk values(3)
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (AMAN.SYS_C005492) violated - parent key not
    found
    
    
    SQL> insert into fk values(null);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select nvl(to_char(a),'null') from fk;
    
    NVL(TO_CHAR(A),'NULL')
    ----------------------------------------
    1
    null
    
    SQL>
    And the answer to this is that null is not compared to any thing. That's why you try to put null in the child table, it is indeed allowed. There is nothing wrong what's happening , this is the actual behavior.

    From the docs,
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/data_int.htm#sthref3045
    >
    Nulls and Foreign Keys

    The relational model permits the value of foreign keys either to match the referenced primary or unique key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.>

    HTH
    Aman....

    Edited by: Aman.... on Apr 30, 2009 11:52 PM
    added doc link

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points