 |
|
| Computers Forum Index » Computer - Databases - IBM DB2 » Constraint problem... |
|
Page 1 of 1 |
|
| Author |
Message |
| --CELKO--... |
 Posted: Thu Oct 29, 2009 1:22 am |
|
|
|
Guest
|
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') |
|
|
| Back to top |
|
|
|
| Lennart... |
| Posted: Thu Oct 29, 2009 5:50 am |
|
|
|
Guest
|
On 29 Okt, 02:22, --CELKO-- <jcelko... at (no spam) earthlink.net> wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
| --CELKO--... |
| Posted: Thu Oct 29, 2009 2:55 pm |
|
|
|
Guest
|
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. |
|
|
| Back to top |
|
|
|
| MarkB... |
| Posted: Fri Oct 30, 2009 8:15 am |
|
|
|
Guest
|
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. |
|
|
| Back to top |
|
|
|
| ChrisC... |
| Posted: Fri Oct 30, 2009 4:21 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| --CELKO--... |
| Posted: Sun Nov 01, 2009 4:21 am |
|
|
|
Guest
|
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. |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Tue Dec 08, 2009 10:22 am
|
|