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,
)
/
CREATE UNIQUE INDEX UX_X_FK_TAB
ON 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?????
)
/
ALTER TABLE X_FK_TAB
ADD CONSTRAINT PK_X_FK_TAB PRIMARY KEY (
)
/
ALTER TABLE X_TEST_TAB
ADD CONSTRAINT FK_X_TEST_TAB_X_FK_TAB FOREIGN KEY (
)
/
insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','1'); insert into X_FK_TAB(FK_NR1,FK_NR2) values('A','2');
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 64BitReceived on Tue Feb 03 2009 - 08:34:15 CST