Re: Can these constraint be implemented in an RDBMS ?

"--CELKO--" <joe.celko_at_northface.edu> wrote in message news: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.

To learn, and so far this thread has learned me things. The views of others, the given solution in Tutorial D and your solution. (Which I'll try to implement as an exercise).

> -- create the account sets; one set per account?
No the customer did not specify this limitation, but from your solution I must be able to construct a solution without this limitation and still follow your construction.

I still do not think that it is practical to implement all constraints (if possible) in an RDBMS but this thread has at least made me think about certain solutions.
(We have to be realistic about riscs, costs and gains.)

(The problem I still have is that some people have such a fundamentalistic standpoint against or for a certain situation; that it is difficult to weight the arguments of those people. I see this with RDBMS people and with OO people. Both groups are totally convinced that they are right, but they have conflicting views.
From my point of view the OO people are wrong on some accounts. But I try to keep an open mind that this might be the case with RDBMS people as well).

Thanks for you solution and your time,
ben brugman

>
> 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.
>
> -- 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 Wed Mar 03 2004 - 15:13:24 CET