Re: Is an FK constraint possible against a non-unique column
- From: Igor Neyman <igor.neyman@xxxxxxxxx>
- To: kcboyes@xxxxxxxxx
- Date: Fri, 23 Jul 2010 14:02:46 -0400
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.
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
