CODEWITHSUNDEEP
sql
Sergey Slepov
Sergey Slepov

Reputation: 2111

How to reverse the dependencies of a foreign key constraint?

Suppose I have two concepts, Teacher and Class. They map nicely to two database tables, Teachers and Classes. To express the statement 'every Class has a Teacher' I add a foreign key to the Classes table pointing to the Teachers table. Now if I try and delete a Teachers row that is used in a Class, I get a nice error saying 'Can't do that' which is what I want.

So far so good. But what if I wanted to extend this behaviour onto Pupils? I.e. whenever I try to delete a Pupil that is part of a class I want the database engine to roar at me and say 'Can't do that'.

I could simulate what I want by creating 30 foreign keys in Classes: Pupil_1_ID, Pupil_2_ID, ... Pupil_30_ID. But what I'm really after is doing this for an arbitrary number of Pupils.

How can I achieve this?

Upvotes: 0

Views: 350

Answers (1)

Code Different
Code Different

Reputation: 93181

Create a surrogate key table, PupilClass that has an FK to Class and another FK to Pupils. This way to you can't delete a pupil who's in a class, and neither can you delete a class that has pupils.

A surrogate table is a common design pattern when you need to model a many-to-many relationships: a class can have many pupils, a pupil can attend many classes.

Upvotes: 4

Related Questions