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

In article <l.carl.pedersen-1110941231210001_at_kip-1-sn-271.dartmouth.edu>, l.carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) wrote:

> 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.
>
RDB permits that form of check constraint. Sorry, I forget what is SQL standard and what is not standard. The mental model I had in mind is of a person filling in a data entry screen for a new loan, and being presented with a list of permissible (legal) loan types, which comes from Type_Pair,i.e.,

            SELECT Loan_Type FROM STUDENT S, TYPE_PAIR TP
            WHERE S.Student_ID = <we know this> 
            AND S.STUDENT_TYPE = TP.STUDENT_TYPE  

I think some variant of the CHECK constraint will do the job.  

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

That's OK. Not to worry.

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

RDB will not allow a deletion from Type_Pair if the above CHECK constraint is violated. But just in case we can formulate a CHECK of the inverse, i.e., we want to ensure there are no rows deleted from Type_Pair if there are outstanding loans of that <Student_Type - Loan_Type>

Check that the number of rows in
  Studen_Loan X Student X Student_Type X Loan X Loan_Type X Type_Pair over Student_Type and Loan_Type in Type_Pair is zero.

>
> is this a referential integrity constraint or not?

Not really. It looks more like a business rule. It's a grey area. I actually enforce these in the front end application by the CHECK clause above rether than in the schema.

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

You will have a hell of a time constructing a data base without referential integrity.
If you don't like enforcing business rules outside of the schema you will hate enforcing
referential entegrity.

> is it somehow less evil to violate the constraint in my example than it
 is to violate
> the simpler constraints classified as referential intregrity?

Yes, it is less evil from a relational perspective. But your end user doesn't care
about that, they just want a system that works.

> why?

That's just the way it is.....Seriously though:

referential integrity is a relational issue, business rules are not. It is left to the application developer to enforce business rules. That's why we get paid big bucks.

Many business rules need to be implemented with a real programming language. Referential
integrity does not do it all, and often SQL doesn't have sufficient power. Stored procedures
in relational systems will help.

Hope that helps......

-- 
Don Baltz
EG&G/Energy Measurements Inc, P.O. Box 1912, Las Vegas, NV  89121
(702) 295-2675               (702) 295-2543 (fax)
e-mail: baltz-dw_at_egg.nv.doe.gov