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