Re: Is an FK constraint possible against a non-unique column
- From: Kevin Hale Boyes <kcboyes@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Fri, 23 Jul 2010 13:06:47 -0600
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
Other related posts:
