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

In <l.carl.pedersen-1010942130400001_at_kip-1-sn-271.dartmouth.edu> l.carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) writes:

>
>suppose i have a student loan system that allows certain types of loans
>only to certain types of students. i might represent this with
>
>create table student
> ( student_id number primary key,
> student_type_id number references student_type,
> name..., ssn... etc. );
>
>create table student_type
> ( student_type_id number primary key,
> student_type_description.... etc.)
>
>create table loan
> ( loan_id number primary key,
> loan_type_id number references loan_type,
> interest_rate... etc.);
>
>create table loan_type
> ( loan_type_id number primary key,
> loan_type_description.... etc.)
>
>create table student_loan
> ( student_id number references student,
> loan_id number references loan,
> amount... etc. );
>
>STUDENT_LOAN tells which loans are held by which students, but what about
>our type restriction saying that only certain types of loans are allowed
>to certain types of students? makes sense to make another table for this:
>
>create table type_pair
> ( student_type_id number references student_type,
> loan_type_id number references loan_type,
> primary key(student_type_id,loan_type_id) );
>
>OK, now what do i do?
>
>for each row in STUDENT_LOAN, i want to enforce the restriction that the
>LOAN_TYPE_ID associated with the loan must appear in the same row of the
>TYPE_PAIR table with the STUDENT_TYPE_ID associated with the student.
>
>i see no way to do this with static referential integrity clauses as
>defined in SQL (89 or 92). i *could* do it with a trigger in ORACLE or
>Sybase, but that seems obnoxious since the constraint is clearly *static*.
>
>i wonder if this lack stems from a similar lack in E-R diagrams? my
>knowledge of the latter is somewhat limited, but in the notations i'm
>familiar with there is no way to state this kind of relationship in an E-R
>diagram either.
>

After creating the type_pair table, in table definition of student_loan, define foreign key constraint to type_pair. This means that student_loan will have three foreign key references. Received on Tue Oct 11 1994 - 19:13:03 CET