Constraint problem

[--CELKO--]

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')

[Karl Schendel]

On Oct 28, 2009, at 9:23 PM, --CELKO-- wrote:

> [snip]

> 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')

In Ingres? probably the best way is to create an insert/update
rule on order_details, passing specifier_duns_nbr and
order_nbr to the DBP. The DB procedure would execute
a select similar to your constraint, and RAISE ERROR if the
condition is not met.

Karl

[--CELKO--]

That was one answer I came up with. In Standard SQL, it would have
been a CREATE ASSERTION statement, since it involves a table level
query. The other was a TRIGGER.