Is an FK constraint possible against a non-unique column

From: Kevin Hale Boyes <kcboyes_at_gmail.com>
Date: Fri, 23 Jul 2010 11:56:30 -0600
Message-ID: <AANLkTiksXYd3fzXcn5=6bargNkOfCN8A4S_Xg+6gQh9Y_at_mail.gmail.com>

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

Received on Fri Jul 23 2010 - 12:56:30 CDT