Re: Is an FK constraint possible against a non-unique column
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: kcboyes@xxxxxxxxx
- Date: Fri, 23 Jul 2010 19:38:00 +0100
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@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:
