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

[Lennart]

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

[--CELKO--]

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.

[MarkB]

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.

[ChrisC]

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

[--CELKO--]

The est answer I got was:

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(1) DEFAULT 'C' NOT NULL
CHECK(specifier_duns_nbr IN ('C', 'U')),
…);
Where C = customer and U = Us in the specifier_duns_nbr. Then we make
a VIEW of Order_Details with a CASE expression :

CASE specifier_duns_nbr
WHEN 'U' THEN '999999999'
WHEN 'C' THEN (SELECTcustomer_duns_nbr

FROM Orders AS O
WHERE O.order_nbr =
Order_Details.order_nbr)

ELSE NULL END

and add an INSTEAD OF TRIGGER and we are done.