From a database theory point of view, a FK points to a PK in another relationship (table). So no, it should not be possible to have a FK to a non-unique column. What you can do is use either a trigger or a check constraint. However, if you need to have a FK pointing to a non-unique columnt, you database design needs normalization. hth Alan.- On Fri, Jul 23, 2010 at 4:06 PM, Kevin Hale Boyes <kcboyes@xxxxxxxxx> wrote: > I'm not sure how the check constraint would work since I can't > reference other tables in a check. > > About the model redesign, I'll give a few more details of the current > design. > > We have purchase orders and invoices that are created against the POs. > A PO goes through a lifecycle: receipt to approval. It can later be > modified and re-approved. > When the PO is approved (or re-approved) it is copied to a different > (APPROVED_PO) table and the original working version is scheduled for > deletion. > Because POs can be amended/modifed and re-approved it is given a > version number in the approval table. > > So, we can have multiple rows in the APPROVED_PO table with the same > PO_ID but different version numbers. > The primary key is (PO_ID, VER). > > An invoice that references a PO can only reference an approved PO. > An invoice also has a lifecycle from initial creation to approval. > When an invoice is created it may be associated with a particular > version of an approved PO. > But until the invoice is approved it actually refers to the last approved > PO. > > So, an invoice is created and associated with version 2 of an approved PO. > The PO is amended and re-approved giving the latest version of 3. > The (unapproved) invoice should refer to the latest version. > > The design we've chosen is to not store the PO version number in the > invoice tables. > When an invoice is loaded by the application it checks for the latest > PO and uses it for validations and messaging to the user. > > So, we want to ensure that the data model constrains the PO_ID field > in the invoice tables against one of the approved POs. > The application will handle which PO is actually being referenced at > the various lifecycle events of the invoice. > > We could have stored the version of the PO in the invoice tables and > that would give us a valid FK reference but the cost > of updating the version across all invoices when a PO is re-approved > wasn't deemed as acceptable. Instead, we choose to > handle the invoices as they are acted upon individually (the invoice > lifecycle events). > > Now, I'm not asking you to re-design our application or anything like > that but maybe this will help explain the decisions we've made a bit > better. > The suggestion by Igor Neyman is a possible solution but I'd like to > avoid it if I can. > > Anyway, thanks to everyone so far for your time and thoughts! > Kevin. > > > On Fri, Jul 23, 2010 at 12:38 PM, Niall Litchfield > <niall.litchfield@xxxxxxxxx> wrote: > > 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 > > > > > > > -- > //www.freelists.org/webpage/oracle-l > > >