Re: Is it Possible to Enforce This Relationship at the DB Level?

"dutone" <dutone_at_hotmail.com> wrote in message news:1192485546.341415.304580_at_q3g2000prf.googlegroups.com...
>> In principle it is possible, assuming your DBMS supports something like
>> SQL's CREATE ASSERTION statement for example.
>
> I'd like to enforce this based on the data model and its
> relationships.

Where you say data model I am sure you mean "logical model" or "conceptual model". Although most people would understand what you mean when you use that term, it is a colloquailism that can/will be misconstrued by readers of this newsgroup. More importantly, if you do take the advice you will get about reading books on fundamentals you will find it easier if you know those books use the term data model for something very different than you expect.

> Although to me, it doesn't seem possible without an additional layer
> of logic.
> The need for a check assertion in a RDMS tells me that cerain
> cituations must be enforced at a higher level.

I suspect you might be thinking that referential integrity constraints should be enough to represent the business rules of your entire enterprise of interest. That is not possible even in principle. There are all kinds of other constraints that one needs, and SQL is supposed to implement some of them (e.g. the check constraints you refer to, and general constraints/assertions). Whether your SQL implementation provides them is another matter, but in any case SQL doesn't provide all the constraint mechanisms you could want, and if it does it may not support them flexibly enough (e.g. by allowing checking to be deferred).

In practice you will have to enforce some assertions in your application, but only because your SQL server doesn't. You should take advantage of whatever limited constraint enforcement your SQL implementation provides, for a whole lot of reasons.

Roy Received on Wed Oct 17 2007 - 09:44:43 CEST