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

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. Received on Tue Oct 11 1994 - 03:30:40 CET