CODEWITHSUNDEEP
mysqlsqlpostgresqlconstraintsreferential-integrity
pathikrit
pathikrit

Reputation: 33459

Referential completeness integrity check in SQL

I have 3 tables - users, teams and team_members. The latter is a many-to-many map from team(id) to user(id) (foreign keys to teams and users respectively). Is there any integrity check I can add to my database that can assert that while teams with no members is possible but users with no teams is impossible? To clarify, I want to enforce at the database layer that all users must belong to atleast 1 team (while no such requirement that all teams must have 1 user). An answer that works in MYSQL or Postgres is acceptable.

Upvotes: 2

Views: 646

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324511

(Answer assumes PostgreSQL; details on triggers and locking will vary if you use another RDBMS, but most RDBMSes should be able to support the same underlying operations.)

While it's possible to add a foreign key from users to teams, doing that requires duplicating knowledge - you'd basically be creating an extra m:1 relationship in addition to the exisitng m:n relationship. That's undesirable.

If you don't need much concurrency, the best option here is probably to use deferred constraint triggers and table locking. Add:

  • a deferred constraint trigger ON INSERT OR UPDATE ... FOR EACH ROW trigger on users that does a LOCK TABLE users, team_members IN EXCLUSIVE MODE and then checks that the created user, if it hasn't since been deleted, has at least one team using a join through team_members. Your application will want to LOCK TABLE users IN EXCLUSIVE MODE before writing to it as well to prevent deadlocks. Note that EXCLUSIVE MODE does not prevent SELECT.

  • a deferred constraint trigger ON UPDATE OR DELETE ... FOR EACH ROW on team_members that does the same thing in reverse, making sure that if you delete a team membership then the user who was a member still has other team memberships. It must also lock both users and team_members.

Of course, team_members also needs FK constraints to users and teams but that should just be assumed for a m:n join table.

If you don't mind having to carefully do things in specific orders, e.g. always add a new membership before deleting an old one, you can use a normal trigger instead of a deferred constraint trigger. That'll give you errors immediately after you do something wrong, instead of at COMMIT time, but will make certain orderings of statements that would otherwise be valid into error conditions.

If you do need good concurrency, you're probably stuffed.

Upvotes: 3

Related Questions