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