1

I have a table chess_games which has the following rows that are relevant for this question:

id | tournamentID | whiteUserID | blackUserID | outcome

I would like to have the following check:

A new tuple may only be inserted, when outcome == -1, tournamentID == NULL and (whiteUserID, blackUserID) is unique.

Note that (whiteUserID, blackUserID) is not a key as it has not to be unique if torunamentID is set or outcome != -1.

Is this possible with a MySQL database? Please also note if the solution is MySQL specific or if it is standard SQL.

Although I would like a solution that works with MySQL, I am also interested in a SQL solution.

Martin Thoma
  • 124,992
  • 159
  • 614
  • 958
  • 4
    not an exact dupe of your question, but the answer comes out the same: mysql doesn't support 'check constraints' (syntax is parsed but otherwise ignored) - use a trigger instead. – Marc B Jul 17 '13 at 18:15
  • @MarcB: Ok, so I need to add a trigger. I've read [what is the difference between triggers, assertions and checks](http://stackoverflow.com/a/2443680/562769), but is there a difference for me if it's a trigger or a check? – Martin Thoma Jul 17 '13 at 18:18
  • in the big picture, checks and triggers are pretty much the same thing. both are automatic responses to events on a table. major diff is that triggers can run arbitrary code and perform other operations (including other insert/update/delete queries). checks are used purely to determine if an operation should be allowed. in short, checks are a simple "yes/no" test if an operation can go ahead, triggers are a "yes/no plus whatever else you need to do". – Marc B Jul 17 '13 at 18:21
  • @moose: The "difference" that you are most concerned with is that MySQL doesn't enforce a CHECK CONSTRAINT. (It's documentation only; MySQL doesn't actually do anything with the CHECK CONSTRAINT.) On the other hand, MySQL will fire a TRIGGER. – spencer7593 Jul 17 '13 at 18:21
  • @MarcB: Could you tell me how to do this with a trigger? I've made a first draft, but I don't know how to complete it: http://pastebin.com/9Vq2K8ny – Martin Thoma Jul 17 '13 at 18:28
  • 1
    http://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger – Marc B Jul 17 '13 at 18:31

0 Answers0