REFERENCEing two columns to the same key

Hi

I'm just wondering whether SQL Server will let me link two columns in one table to the same column in another table. Basically, my table looks like this:

BOOKINGS
-----
BookingID (PK)
BorrowerID (FK1)
ModuleID (FK2)
LecturerID (FK3)
CollectionDateTime
ReturnDateTime
Authorised
TakenUp

FK1 and FK3 are related to BorrowerID - both lecturers and students are stored in the same table because lecturers are able to book and borrow equipment as well as students. Will SQL Server let me create a Foreign Key on BorrowerID and LecturerID that both point to Borrowers.BorrowerID?

My understanding of Foreign Keys is that they're just a way of enforcing referential integrity, so they'll only come into play when data is being deleted that might cause orphaned rows in the dependent table. So there shouldn't be too much of a problem with this, is that right?

Thanks
JonYes, absolutely. SQL Server will let you link FK1 and FK3 to the same table. However, if you want to enforce referential integrity systematically (ie cascading updates and deletes), you will only be able to do so on one of the foreign keys. With the other foreign key you will have to use a trigger. This is one of SQL's annoying "personalities" .

Terri|||Cool - thanks for your reply

You might be able to tell, but I'm quite a SQL novice. I'm guessing the trigger would be in effect on INSERTs and would check that one of the Foreign Keys is a valid value? Would it need to be effective on anything else? I guess UPDATES, but what about DELETES?

Great, now I have to learn triggers!!!

Cheers
Jon|||This KB article describes the problem you will encounter:PRB: Error Message 1785 Occurs When You Create a FOREIGN KEY Constraint That May Cause Multiple Cascade Paths

So, in your case you cannot use what is referred to as Declarative Referential Integrity (DRI).

This is an area where I am not particularly knowledgeable. I have not used DRI nor triggers for referential integrity. For better or worse, the stored procedure doing the data modification is where I have put such code.

Terri