How to enforce the mandatory participation constraint at the many end in a one-to-many relationship?

Question

Say I have the following ER diagram:

Enforcing the mandatory participation constraint at the one end is easy, I simply make the foreign key (school_id) in Student NOT NULL.

But how can I enforce the mandatory participation constraint at the many end (each School must at least have one Student), that is, how can I prevent something like this:

If I inserted a School row first, it will obviously have no Students.

And I also can't insert a Student row first with a school_id that doesn't exist in the School table (referential integrity constraint will prevent it) and then insert a School row (with the same school_id).

Note: I am using MySQL.

Answer 1

It is IMHO not the best idea trying to enforce such a constraint through a database mechanism, it will lead you into all sorts of trouble like the one you already mentioned. Or, in different word, avoid such kind of relationships in an ERM.

However, if you are really insist to enforce it, I guess you can do this by implementing some triggers, preventing any direct insertion of new schools, or deletion of students. Instead, you need to provide some stored procedures for adding new schools only in combination with one student, and for refusing to delete the last student of a school (or deleting the school automatically when the last student is deleted). The first of these two stored procedures needs to switch off the triggers temporarily to avoid the chicken/egg problem you mentioned.

Answer 2

In a conceptual model everything is possible, but in reality that relationship cannot be enforced because of this situation: you cannot insert student because school doesn't exist and you cannot inset school because student doesn't exist. That's way the mandatory end is always the one-end.

Although in your example I would suggest neither end to be mandatory since students can exist apart from a school and that would allow you to insert a student in the table and defer the asigning a school for him/her.

Answer 3

I think I would do it with a view.

realschools = select * from school inner join student on school.id = student.schoolId

Rename the school table to 'potentialSchools' or something and the inner join will mean that a soon as a potentialSchool has no students it will disappear from the school view