Re: Is an FK constraint possible against a non-unique column
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: kcboyes@xxxxxxxxx
- Date: Fri, 23 Jul 2010 14:40:44 -0500
Well, I cant quite visualize what you are describing, but I suspect you
could achieve the desired effect with a trigger.
On Fri, Jul 23, 2010 at 2: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
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
