Re: Composite Foreign key question

You cannot create foreign key references the first field of table A if it is not unique itself.
If it is allowed table A has only unique pairs (A,B) you should not create foreign key to the first field, because you can not.

You can make:

CREATE TABLE A (
A NUMBER,
B NUMBER,
PRIMARY KEY (A, B)
);

create table B (
A NUMBER,
B NUMBER,
CONSTRAINT BOTH_A_B FOREIGN KEY (A,B) REFERENCES A (A,B) );

create or replace trigger B_Ins
before insert or update
ON B
FOR EACH ROW
DECLARE
 A_A NUMBER;
BEGIN
 select distinct A into A_A from A where A.A=:new.A;  EXCEPTION
 WHEN NO_DATA_FOUND THEN
 raise_application_error (-20291, 'A is missing'); END;
/

And now if you had:

SQL> select * from A;

        A B
--------- ---------

        1 2

You will:

SQL> insert into B values (1,2);

1 row created.

SQL> insert into B values (1, 3);
insert into B values (1, 3)

            *
ERROR at line 1:
ORA-02291: integrity constraint (DEG.BOTH_A_B) violated - parent key not found

SQL> insert into B values (1, NULL);

1 row created.

SQL> insert into B values (2, NULL);
 insert into B values (2, NULL)

             *
ERROR at line 1:

ORA-20291: A is missing
ORA-06512: at "DEG.B_INS", line 8
ORA-04088: error during execution of trigger 'DEG.B_INS'

You can catch you error code in your application.

"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> a écrit dans le message news: 3be0284f.1735268578_at_news.alt.net...
> 1) There are two table that have two columns. Both of the second
> table's columns reference both of the first table's columns. But the
> second column in the SECOND table can be NULL.
>
> CREATE TABLE A (
> A NUMBER,
> B NUMBER,
> PRIMARY KEY (A, B)
> );
>
> CREATE TABLE B (
> A NUMBER CONSTRAINT Just_A REFERENCES A,
> B NUMBER,
> CONSTRAINT Both_A_And_B FOREIGN KEY (A, B) REFERENCES A (A, B)
> );
>
> The "real" FOREIGN KEY is Both_A_And_B. However, since in one case B
> can be NULL, I need the other FOREIGN KEY (Just_A) to keep things
> straight.
>
> I think that would be how to do it. Any comments? I'd like to know if
> I am missing something.
>
> 2) There are two table that have two columns. Both of the second
> table's columns reference both of the first table's columns. But the
> second column in the FIRST table can be NULL.
>
> CREATE TABLE A (
> A NUMBER PRIMARY KEY,
> B NUMBER,
> UNIQUE (A, B)
> );
>
> CREATE TABLE B (
> A NUMBER,
> B NUMBER,
> PRIMARY KEY (A, B)
> CONSTRAINT Both_A_And_B FOREIGN KEY (A, B) REFERENCES A (A, B)
> );
>
> How could I FOREIGN KEY A, B to reference A,B (in the first table)
> unless B is NULL in the first table, in which I would only want to
> FOREIGN KEY A.
>
> Brian
Received on Thu Nov 01 2001 - 04:56:47 CST