 |
|
| Computers Forum Index » Computer - Databases - Ingres » Constraint problem... |
|
Page 1 of 1 |
|
| Author |
Message |
| --CELKO--... |
 Posted: Thu Oct 29, 2009 1:23 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 |
|
|
|
| Karl Schendel... |
| Posted: Thu Oct 29, 2009 2:30 pm |
|
|
|
Guest
|
On Oct 28, 2009, at 9:23 PM, --CELKO-- wrote:
Quote: [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 |
|
|
| Back to top |
|
|
|
| --CELKO--... |
| Posted: Thu Oct 29, 2009 2:52 pm |
|
|
|
Guest
|
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. |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Wed Mar 17, 2010 7:41 am
|
|