Re: problem with referential integrity notation in SQL (and in ER diagrams?)

In article <baltz-dw-1110940825010001_at_149.252.239.66>, baltz-dw_at_egg.nv.doe.gov (Donald W. Baltz) wrote: [snip]
>
> CHECK ((SELECT COUNT(*) FROM STUDENT S, TYPE_PAIR TP
> WHERE S.STUDENT_TYPE = TP.STUDENT_TYPE) > 0 )
>

do any implementations actually allow this? i know oracle doesn't allow stuff like that in a CHECK constraint.

actually, i think what i want is more complex than what you say. if it's a global constraint, applying to the entire system, it would have to be something like:

check not exists
  (select s.student_type, l.loan_type

     from student s, loan l, student_loan sl     where s.student_id = sl.student_id and

          l.loan_id = sl.loan_id)
   minus
   select student_type, loan_type
    from type_pair)

> WHAT YOU HAVE HERE IS A BUSINESS RULE. THE CLASSIC EXAMPLE OF A BUSINESS
> RULS IS THAT THE SALARY OF AN EMPLOYEE CANNOT BE GREATER THAN THE SALARY OF
> THE SUPERVISOR. NOW, E-R DIAGRAMS WERE NEVER INTENDED TO MODEL BUSINESS
 RULES.
> THE PROBLEM IS NOT A LIMITATION OF THE E-R NOTATION, IT IS THE MISGUIDED
 ATTEMPT
> OF TRYING TO GET MORE INFORMATION OUT OF THEM THAN WHAT
> THEY WERE DESIGNED TO PROVIDE.
maybe what i'm really questioning is the concept of referential integrity. most of my primary keys end up being referenced as a foreign key somewhere. however, there is no place where i get to do that with my type_pair table.

i could imagine having information in my type_pair table, maybe different rules apply to different types of people when they get different types of loans. i should not be allowed to delete a row from the type_pair table while such combinations actually exist in my system.

is this a referential integrity constraint or not? if it's not, then what's so special about referential integrity - as opposed to any other business rule, i.e., why is it held in such sacred regard? is it somehow less evil to violate the constraint in my example than it is to violate the simpler constraints classified as referential intregrity? why? Received on Tue Oct 11 1994 - 18:31:21 CET