Prev: how many records contribute to a page.
Next: Please help! set integrity using system tablespace
From: --CELKO-- on 28 Oct 2009 21:22
This is an actual problem in a manufacturing environment. An order
goes to one and only one customer. An order will have products that
are specified by either the customer or by us; there are no other
sources. We use DUNS numbers to identify companies, both customers
and ourselves (assume our DUNS = '999999999'); there are no other
sources.

The skeletons of the tables involved look like this:

CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
customer_duns_nbr CHAR(9) NOT NULL
REFERENCES Companies (duns_nbr),
…);


CREATE TABLE Order_Details
(order_nbr INTEGER NOT NULL
REFERENCES Orders(order_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
item_nbr INTEGER NOT NULL,
PRIMARY KEY (order_nbr, item_nbr)
specifier_duns_nbr CHAR(9) NOT NULL,
REFERENCES Companies (duns_nbr),
…);

What is the best way to enforce the constraint which we could write in
Full-92 as a table constraint:

CHECK (specifier_duns_nbr
IN ((SELECT customer_duns_nbr
FROM Orders AS O
WHERE O.order_nbr = Order_Details.order_nbr),
'999999999')
From: Lennart on 29 Oct 2009 01:50
On 29 Okt, 02:22, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> This is an actual problem in a manufacturing environment.  An order
> goes to one and only one customer.  An order will have products that
> are specified by either the customer or by us; there are no other
> sources.  We use DUNS numbers to identify companies, both customers
> and ourselves (assume our DUNS = '999999999'); there are no other
> sources.
>
> The skeletons of the tables involved look like this:
>
> CREATE TABLE Orders
> (order_nbr INTEGER NOT NULL PRIMARY KEY,
>   customer_duns_nbr CHAR(9) NOT NULL
>        REFERENCES Companies (duns_nbr),
>  …);
>
> CREATE TABLE Order_Details
> (order_nbr INTEGER NOT NULL
>    REFERENCES Orders(order_nbr)
>    ON UPDATE CASCADE
>    ON DELETE CASCADE,
>  item_nbr INTEGER NOT NULL,
>  PRIMARY KEY (order_nbr, item_nbr)
>  specifier_duns_nbr CHAR(9) NOT NULL,
>        REFERENCES Companies (duns_nbr),
>  …);
>
> What is the best way to enforce the constraint which we could write in
> Full-92 as a table constraint:
>
> CHECK (specifier_duns_nbr
>         IN ((SELECT customer_duns_nbr
>               FROM Orders AS O
>              WHERE O.order_nbr = Order_Details.order_nbr),
>             '999999999')


I can only think of two ways (you might notice the absence of good :-)
either via triggers, or by adding a dummy order for your company and
use a foreign key. In the latter case I would then add check
constraints in referencing tables that prevents the usage of this
dummy order other than for specifier_duns_nbr.


/Lennart
From: --CELKO-- on 29 Oct 2009 10:55
I thought of a trigger, but do not like it -- procedural code, no very
portable in spite of the SQL/PSM standards, etc. The dummy order
will not work; this is aerospace and the whole order has to stay
together for the audit trail.
From: MarkB on 30 Oct 2009 04:15
Can you use NULL values for ORDER_DETAILS.specifier_duns_nbr instead
of '999999999'?
If so it can be:
---
create table Orders (
order_nbr int not null primary key
, customer_duns_nbr char(9) not null
....
, unique (order_nbr, customer_duns_nbr)
);

create table Order_Details (
order_nbr int not null
references Orders(order_nbr)
on delete cascade
, item_nbr int not null
, specifier_duns_nbr char(9)
....
, primary key (order_nbr, item_nbr)
, foreign key (order_nbr, specifier_duns_nbr)
references Orders(order_nbr, customer_duns_nbr)
on delete cascade
);
---

Sincerely,
Mark B.
From: ChrisC on 30 Oct 2009 12:21
Unfortunately you can't use a view or a MQT in a references clause,
otherwise those would be nice. Other options that will work, but
probably aren't the 'best', are:
- Use a trigger on the table to do the lookup or check for the hard
value.
- Use a trigger to lookup the value in the Orders table unioned with a
table containing your identifiers.
- Build another table that is somehow maintained, probably via
triggers, with the current valid customers out of Orders plus you
identifiers, and setup a references claus on that table.

Maybe one of those will help,
Chris
 |  Next  |  Last
Pages: 1 2
Prev: how many records contribute to a page.
Next: Please help! set integrity using system tablespace