Re: The fable of DEMETRIUS, CONSTRAINTICUS, and AUTOMATICUS

From: Tony Andrews <andrewst_at_onetel.com>
Date: 19 Oct 2004 06:23:39 -0700
Message-ID: <1098192219.279174.101130_at_c13g2000cwb.googlegroups.com>


Laconic2 wrote:
> Tony,
> Maybe you can help me out here.
>
> A lot of the current discussion seems to be turning on the issue of
> constraints, and basically
> whether you express them as DDL, or as metadata, or as code that
enforces
> them.
>
> But it's my impression that in the "ultimate normal form", all of
the
> constraints follow from the definitions of the keys and of the
domains. So,
> isn't it the case that all the constraints like "person must be 18
years or
> older on start date" could be subsumed into the definition of the
keys and
> the domains?
>
> Where am I going wrong here?

I am not sure, but I certainly don't think all rules follow from domain and key definitions. Domains enforce some static rules ("salary must be a number between 0 and 1000000" for example), but not rules that require knowledge of other data (e.g. "employee salary cannot exceed manager's salary"). Foreign key constraints implement a subset of possible multi-table rules, e.g. "employee.deptno must correspond to a department.deptno", but not all conceivable constraints, e.g. "employee.deptno must correspond to a department.deptno where employee.hiredate between department.open_date and department.close_date".

ANSI SQL has a concept called an ASSERTION that enable such complex constraints to be declared, but it is not widely implemented (e.g. not by Oracle). Consequently one is often forced into using database triggers or stored procedures to enforce them. Received on Tue Oct 19 2004 - 15:23:39 CEST

Original text of this message