Re: Can these constraint be implemented in an RDBMS ?
Date: 2 Mar 2004 12:02:15 -0800
Message-ID: <a264e7ea.0403021202.7bf84cc_at_posting.google.com>
>> In another thread I was asked to produce an example which can not
be implemented with RDBMS constraints. <<
I am not sure what you want here.
CREATE TABLE RetirementPlan
(winning_horse_name VARCHAR (35)NOT NULL
CHECK (winning_horse_name = <<TOMORROW'S WINNER>> ));
But that is silly.
2) In standard SQL, there is a CREATE ASSERTION statement that it a CHECK() constraint attached to the whole schema and not to any table. The problem it solves is that all constraints on an empty table are TRUE (Existential import is not part of modern logic). This also lets you have multiple table constraints.
- create the account sets; one set per account? CREATE TABLE Accounts (acct_nbr INTEGER NOT NULL PRIMARY KEY, acct_set_id INTEGER NOT NULL REFERENCES AccountSetLmits(acct_set_id), amount DECIMAL(12,4) NOT NULL);
--table of range for sets
CREATE TABLE AccountSetLimits
(acct_set_id INTEGER NOT NULL PRIMARY KEY
REFERENCES AccountSets(acct_set_id),
min_amt DECIMAL (12,4) DEFAULT 0.00 NOT NULL,
max_amt DECIMAL (12,4) NOT NULL,
CHECK (min_amt <= max_amt));
--now assert that no set is out of its range
CREATE ASSERTION ValidateSetLimits
CHECK (NOT EXISTS
(SELECT * FROM Accounts AS A1, AccountSetLimits AS L1 WHERE A1.acct_set_id = L1.acct_set_id GROUP BY A1.acct_set_id HAVING SUM(A1.amount) NOT BETWEEN L1.min_amt AND L1.max_amt));
Oracle's RDB is one of the few RDBMS products that had this. Received on Tue Mar 02 2004 - 21:02:15 CET