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

On Feb 3, 9:34*am, Norbert Winkler <norbert.winkl... (AT) gmx (DOT) de> wrote:
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 --

Comments embedded.

On Feb 3, 8:34*am, Norbert Winkler <norbert.winkl... (AT) gmx (DOT) de> wrote:
No, there isn't. A unique index is not the same as a unique/primary
key.

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

"Norbert Winkler" <norbert.winkler1 (AT) gmx (DOT) de> wrote

A foreign key that includes a nullable column does not compare nulls between
the two tables. Instead a multi-column constraint will never be violated on
a row that contains a null - even if the value(s) in the other column(s) are
not present in the referenced table. Nullable foreign keys are pretty
useless in most cases.

I suggest you do without a nullable foreign key. Have one table with the
constraint on one column and another with the constraint on two.

--
David Portas