6

Say I have the following ER diagram:

enter image description here

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:

enter image description here

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.

  • 3
    Does such a restriction even make logical sense? If you create two new students that each are going to a different school, they need a school_id at creation. How do you intend to ensure that each student ends up in the right school? I think that creating a school with no assigned students is the safest thing, from a data integrity standpoint. But why don't you want to do this? – Thomas Owens Nov 6 '16 at 23:27
  • @Thomas Owens I am currently learning about databases, and I have seen an ER diagram such as the one in my question, and I am wondering how such a relationship can be implemented in an actual database (even though in this case you are right, this doesn't make logical sense). – Tom Nov 7 '16 at 0:08
  • 1
    I'd be interested in seeing the context. It makes sense as a representation of business rules that say that every school needs at least one student, but not as a rule that would be enforced in a database (or at least the tables that you have defined - perhaps a bit flag in the school table to indicate if there are students would be included). – Thomas Owens Nov 7 '16 at 0:14
  • I have an issue with the given data model - it's quite possible for the student to be enrolled in/taking classes at multiple schools concurrently (I've done it). It's also possible that a student may not yet be enrolled in any school (for instance, just moved to a new town, and the school board sends a notice saying "pick one of these five", or something). You have a many/many relationship, which should be addressed with a cross-ref table, and having no matches would be the subject of a report. If you have multiple of something, you usually are able to have none of it. – Clockwork-Muse Nov 7 '16 at 7:58
2

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.

0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.