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

Seems like the only way to achieve this is to put detail_id into separate
table (making it a PK in that new table) , and then FK from DETAIL and
OTHER_TAB to this new detail lookup table.

Regards,
Igor Neyman

On Fri, Jul 23, 2010 at 1:56 PM, Kevin Hale Boyes <kcboyes@xxxxxxxxx> 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
>
>
>

Other related posts: