foreign constraint with unique key

From: Norbert Winkler <norbert.winkler1_at_gmx.de>
Date: Tue, 3 Feb 2009 15:34:15 +0100
Message-ID: <1v3o377vzqhj5.1ll05haaw4xyw.dlg_at_40tude.net>



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
Received on Tue Feb 03 2009 - 08:34:15 CST

Original text of this message