2

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.

pathikrit
  • 32,469
  • 37
  • 142
  • 221
  • What database are you using? MySQL or Postgres? – Gordon Linoff Mar 31 '15 at 02:50
  • I am open to either. – pathikrit Mar 31 '15 at 02:50
  • Just a thought. Your constraint means that when you try to `INSERT` a row into `users`, you have to also `INSERT` at least one row into `team_members`. Thus, the whole operation is not atomic and the constraint check has to be deferred until both tables are changed. It smells like some fancy trigger. Would be interested to see a solution. – Vladimir Baranov Mar 31 '15 at 03:19
  • The same problem solved in this related answer: http://stackoverflow.com/questions/8394177/complex-foreign-key-constraint-in-sqlalchemy/8395021#8395021 and: http://stackoverflow.com/questions/24813000/how-to-deal-with-mutually-recursive-inserts/24816197#24816197 (possible duplicate) – Erwin Brandstetter Mar 31 '15 at 05:53
  • Teams without members and users that are not part of a team are a minor problem, IMO. Forcing such a constraint will only make things difficult. (formation of new teams. transfer of players between teams, leaving one team temporally empty, or a user without a team). Better allow the orphans and add a mechanism to track&trace them. – joop Mar 31 '15 at 08:38
  • @ErwinBrandstetter: How did you find these? I thought I did a thorough search before to make sure I don't post a duplicate. – pathikrit Mar 31 '15 at 16:28
  • @joop: Ofcourse I can do all this in the app layer (which is what I currently do) but I was just wondering if there was an "easy" way to do it in db layer too... – pathikrit Mar 31 '15 at 16:29
  • @VladimirBaranov: Yes, I am aware of that - when you create a user, you must also assign him a team. – pathikrit Mar 31 '15 at 16:29
  • 1
    @wrick: How I found those? Well, I wrote them, so I knew how to find them. A search for [`[postgres] [referential-integrity] foreign key deferrable`](http://stackoverflow.com/search?q=[postgres]+[referential-integrity]+foreign+key+deferrable) is a good start ... – Erwin Brandstetter Mar 31 '15 at 17:38

1 Answers1

3

(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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • If you didn't need `team_members` then could you do it with an array column to replace `team_members`, some triggers to fake the FK, and a CHECK to ensure that the array isn't empty? Race conditions? – mu is too short Mar 31 '15 at 04:26
  • 1
    @muistooshort You can, but FK triggers have some special tricks that you can't easily emulate with user-defined triggers. Races would be a problem, as you suspected, unless you took table-level locks. In which case you might as well do the conventional relational modelling. – Craig Ringer Mar 31 '15 at 04:36
  • Well this rules out Mysql since it does not have deferred constraints – pathikrit Mar 31 '15 at 16:30
  • 1
    @muistooshort: Someone once wrote a plugin for "array of foreign keys" for postgres: http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ – pathikrit Mar 31 '15 at 16:30
  • 1
    @wrick That's actually a patch for PostgreSQL core, you can't do that with plugins. It had some *major* performance issues and was not accepted into PostgreSQL 9.3. – Craig Ringer Apr 01 '15 at 01:59