Oracle Database Discussions

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!!!!