Re: Is an FK constraint possible against a non-unique column

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 23 Jul 2010 19:38:00 +0100
Message-ID: <AANLkTinGPPMyumiFskq9nAVtZLgRwaK7B3ViCQSbH07t_at_mail.gmail.com>

That sounds like a Check constraint using exists. Or a model redesign.

Niall Litchfield

On Jul 23, 2010 6:58 PM, "Kevin Hale Boyes" <kcboyes_at_gmail.com> wrote:

I have a table that uses a composite primary key. On its own, DETAIL_ID is not unique.

CREATE TABLE DETAIL (
   DETAIL_ID NUMBER NOT NULL,
   LINE NUMBER NOT NULL,
   CONSTRAINT XPKDETAIL PRIMARY KEY (DETAIL_ID, LINE)
);

In another table I'd like to have a column, DETAIL_ID, that has its values constrained by the DETAIL table.
The constraint is that there must be at least one row in DETAIL with the corresponding DETAIL_ID.
There could be more than one but there must be at least one for the DETAIL_ID to be used in this other table.

If I could, I'd define it as follows:

CREATE TABLE OTHER_TAB (

   OTHER_TAB_ID      NUMBER NOT NULL,
   DETAIL_ID          NUMBER NOT NULL,

   CONSTRAINT XPKOTHER_TAB PRIMARY KEY (OTHER_TAB_ID),    CONSTRAINT FKDETAIL FOREIGN KEY (DETAIL_ID) REFERENCES DETAIL (DETAIL_ID)
);

But of course, this produces ORA-02270: no matching unique or primary key for this column-list

Is there any way to do what I'm after?

Thanks,
Kevin.

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 23 2010 - 13:38:00 CDT