Re: Can these constraint be implemented in an RDBMS ?

From: --CELKO-- <joe.celko_at_northface.edu>
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.

  1. You can have a non-deterministic constraint:

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.

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

Original text of this message