Re: foreign constraint with unique key

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 3 Feb 2009 06:51:48 -0800 (PST)
Message-ID: <76ae0038-a2d1-49d1-b31c-57048347c783_at_i24g2000prf.googlegroups.com>

On Feb 3, 9:34 am, Norbert Winkler <norbert.winkl..._at_gmx.de> wrote:
> Hi,
>
> I've tried to create a foreign key on two columns, where the second col
> should be nullable to avoid a trigger for testing integrity.
>
>   drop TABLE X_TEST_TAB;
>   drop TABLE X_FK_TAB;
>
>   CREATE TABLE X_FK_TAB (
>     FK_NR1 VARCHAR2(15) NOT NULL,
>     FK_NR2 VARCHAR2(5)  NULL
>   )
>   /
>   CREATE TABLE X_TEST_TAB (
>     FK_NR1   VARCHAR2(5) NOT NULL,
>     FK_NR2   VARCHAR2(5) NULL,
>     ANY_INFO VARCHAR2(5) NULL
>   )
>   /
>   CREATE UNIQUE INDEX UX_X_FK_TAB
>     ON X_FK_TAB (
>       FK_NR1,FK_NR2
>     )
>   /
>   insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','1');
>   insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','2');
>   insert into X_TEST_TAB(FK_NR1,FK_NR2,any_info) values('A','1','???');
>
>   -- I could insert this:
>   insert into X_FK_TAB(FK_NR1) values('B');
>   insert into X_TEST_TAB(FK_NR1,any_info) values('B','!!!!');
>
>   ALTER TABLE X_TEST_TAB
>     ADD CONSTRAINT FK_X_TEST_TAB_X_FK_TAB FOREIGN KEY (
>       FK_NR1,
>       FK_NR2
>     ) REFERENCES X_FK_TAB (
>       FK_NR1,
>       FK_NR2
>     )
>   /  
> this produces
> ORA-02270: no matching unique or primary key for this column-list
> but there is a unique key?????
>
> 2. example with primary key
>
>   CREATE TABLE X_FK_TAB (
>     FK_NR1 VARCHAR2(15) NOT NULL,
>     FK_NR2 VARCHAR2(5)  NOT NULL -- not null the PK adds if omitted
>   )
>   /
>   CREATE TABLE X_TEST_TAB (
>     FK_NR1   VARCHAR2(5) NOT NULL,
>     FK_NR2   VARCHAR2(5) NULL,
>     ANY_INFO VARCHAR2(5) NULL
>   )
>   /
>   ALTER TABLE X_FK_TAB
>     ADD CONSTRAINT PK_X_FK_TAB PRIMARY KEY (
>       FK_NR1,
>       FK_NR2
>     )
>   /
>   ALTER TABLE X_TEST_TAB
>     ADD CONSTRAINT FK_X_TEST_TAB_X_FK_TAB FOREIGN KEY (
>       FK_NR1,
>       FK_NR2
>     ) REFERENCES X_FK_TAB (
>       FK_NR1,
>       FK_NR2
>     )
>   /
>   insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','1');
>   insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','2');
>   -- can not do this: insert into X_FK_TAB(FK_NR1) values('B');
>
>   insert into X_TEST_TAB(FK_NR1,FK_NR2,any_info) values('A','1','???');
>
> --
> Norbert
> Oracle9i Enterprise Edition Release 9.2.0.8.0 64Bit

I am not 100% sure of your exact question but a unique index is not a Unique Key or Primary Key constraint. Oracle uses indexes to enforce PK and UK constaints but it is actually possible for those indexes to be non-unique since version 8.0.

A Constraint is an object in its own right and must exist per the documentation as referenced in the error message. A enabled constrain will have an index used to support it, but the index and the constraint are not one and the same.

HTH -- Mark D Powell -- Received on Tue Feb 03 2009 - 08:51:48 CST