Re: foreign constraint with unique key

From: ddf <oratune_at_msn.com>
Date: Tue, 3 Feb 2009 12:02:47 -0800 (PST)
Message-ID: <6f605446-74c2-4a7a-a56a-03c467051744_at_z27g2000prd.googlegroups.com>

Comments embedded.

On Feb 3, 8: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?????
>

No, there isn't. A unique index is not the same as a unique/primary 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

Let's run your example with some comments:

SQL>
SQL> CREATE TABLE X_FK_TAB (
  2 FK_NR1 VARCHAR2(15) NOT NULL,
  3 FK_NR2 VARCHAR2(5) NULL
  4 )
  5 /

Table created.

SQL>
SQL> CREATE TABLE X_TEST_TAB (

  2  FK_NR1   VARCHAR2(5) NOT NULL,
  3  FK_NR2   VARCHAR2(5) NULL,
  4  ANY_INFO VARCHAR2(5) NULL

  5 )
  6 /

Table created.

SQL>
SQL> --
SQL> -- Unique index, not unique key
SQL> --
SQL> CREATE UNIQUE INDEX UX_X_FK_TAB

  2 ON X_FK_TAB (
  3 FK_NR1,FK_NR2
  4 )
  5 /

Index created.

SQL>
SQL> --
SQL> -- No unique/primary key constraints
SQL> --
SQL> SELECT constraint_name, constraint_type

  2 from user_constraints
  3 where table_name = 'X_FK_TAB'
  4 and constraint_Type in ('U','P')
  5 /

no rows selected

SQL>
SQL> insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','1');

1 row created.

SQL> insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','2');

1 row created.

SQL> insert into X_TEST_TAB(FK_NR1,FK_NR2,any_info) values ('A','1','???');

1 row created.

SQL>
SQL>
SQL> -- I could insert this:
SQL> --
SQL> -- No primary key constraint to set NOT NULL attribute of
SQL> -- all indexed columns
SQL> --
SQL> insert into X_FK_TAB(FK_NR1) values('B');

1 row created.

SQL> insert into X_TEST_TAB(FK_NR1,any_info) values('B','!!!!');

1 row created.

SQL>
SQL> --
SQL> -- Foreign key will not create
SQL> --
SQL> --
SQL> -- No constraint to reference
SQL> --
SQL> ALTER TABLE X_TEST_TAB

  2 ADD CONSTRAINT FK_X_TEST_TAB_X_FK_TAB FOREIGN KEY (   3 FK_NR1,
  4 FK_NR2
  5 ) REFERENCES X_FK_TAB (
  6 FK_NR1,
  7 FK_NR2
  8 )
  9 /
  FK_NR1,
  *
ERROR at line 6:
ORA-02270: no matching unique or primary key for this column-list

SQL>
SQL> --
SQL> -- Let's try again
SQL> --
SQL> drop TABLE X_TEST_TAB purge;

Table dropped.

SQL> drop TABLE X_FK_TAB purge;

Table dropped.

SQL>
SQL> CREATE TABLE X_FK_TAB (
  2 FK_NR1 VARCHAR2(15) NOT NULL,
  3 FK_NR2 VARCHAR2(5) NOT NULL -- not null the PK adds if omitted   4 )
  5 /

Table created.

SQL>
SQL> CREATE TABLE X_TEST_TAB (

  2    FK_NR1	VARCHAR2(5) NOT NULL,
  3    FK_NR2	VARCHAR2(5) NULL,

  4 ANY_INFO VARCHAR2(5) NULL
  5 )
  6 /

Table created.

SQL>
SQL> ALTER TABLE X_FK_TAB
  2 ADD CONSTRAINT PK_X_FK_TAB PRIMARY KEY (

  3  	 FK_NR1,
  4  	 FK_NR2

  5 )
  6 /

Table altered.

SQL>
SQL> --
SQL> -- Constraint exists
SQL> --
SQL> SELECT constraint_name, constraint_type

  2 from user_constraints
  3 where table_name = 'X_FK_TAB'
  4 and constraint_Type in ('U','P')
  5 /

CONSTRAINT_NAME                C
------------------------------ -
PK_X_FK_TAB                    P

SQL>
SQL> --
SQL> -- Foreign key creates

SQL> --
SQL> ALTER TABLE X_TEST_TAB
  2 ADD CONSTRAINT FK_X_TEST_TAB_X_FK_TAB FOREIGN KEY (

  3  	 FK_NR1,
  4  	 FK_NR2
  5    ) REFERENCES X_FK_TAB (
  6  	 FK_NR1,
  7  	 FK_NR2

  8 )
  9 /

Table altered.

SQL>
SQL> insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','1');

1 row created.

SQL> insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','2');

1 row created.

SQL>
SQL> -- can not do this:
SQL> --
SQL> -- Primary key also sets NOT NULL attribute
SQL> -- for all key columns
SQL> --
SQL> insert into X_FK_TAB(FK_NR1) values('B');

insert into X_FK_TAB(FK_NR1) values('B') *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("BING"."X_FK_TAB"."FK_NR2")

SQL>
SQL>
SQL> insert into X_TEST_TAB(FK_NR1,FK_NR2,any_info) values

('A','1','???');

1 row created.

SQL> David Fitzjarrell Received on Tue Feb 03 2009 - 14:02:47 CST