MySQL: define uniqueness over multiple tables

Question

I am starting to develop an application in which user can create bets. Given the following scheme

TABLE Player
  PlayerID PRIMARY KEY
  PlayerName 
  (...)

TABLE Bet
  BetID PRIMARY KEY
  BetName
  (...) 

TABLE plays_in
  BetID
  PlayerID
  PRIMARY KEY(BetID, PlayerID)
  FOREIGN KEY BetID
  FOREIGN KEY PlayerID

Is it in any way possible to define an uniqueness for (BetName, PlayerID), in order that a Bet can have the same name multiple times, but only once for a player? Meaning that a certain player can participate only once in a bet named "MyFirstBet"? I don't want to define the bet name as a primary key or unique, because any other player that will not play this bet with the player above, should be able to name his bet "MyFirstBet" once too. If possible, I want to avoid to create an extra table for that too. Is this a problem to solve in code and not in the DBMS?

score 4 · Accepted Answer · answered Jun 02 '13 at 00:58

4

Move "BetName" to the table "plays_in".

TABLE plays_in
  BetName
  PlayerID
  PRIMARY KEY(BetName, PlayerID)
  FOREIGN KEY PlayerID

Then drop the table "Bet".

answered Jun 02 '13 at 00:58

Mike Sherrill 'Cat Recall'

91,602
17
122
185

This solution would be nice, but unfortunately, there will be more rows in the table Bet than shown above, should have made this more clear. – Valentino Ru Jun 02 '13 at 10:51

user2246674 · Answer 2 · 2013-06-02T06:13:12.723

This model is not supported by standard SQL - this issue is that BetName is not a Key and is not (currently) consider part of a Candidate Key.

One way to solve this problem keeping the relations as they are and ensuring referential integrity is to add a column BetName to PlaysIn and then have PlayIn FK(BetId, BetName) so there is a FK over a Candidate Key and not just the Surrogate PK. Next, add a PlaysIn UX(BetName, PlayerId) to enforce the unique name/player. Basically, it constrains the relations by means of the Surrogate and appropriate compound Key. This is sort of icky because there is not "duplicate data" (outside of a Compound PK) used for RI.

TABLE Bet
  PK BetID
  BetName 

TABLE PlaysIn
  BetID
  PlayerID
  BetName -- must set
  PK (BetID, PlayerID)
  FK Bet(BetID, BetName)
  UX (PlayerID, BetName)

Another approach, which I recommend although it does change the relationships, is to move PlayerId out of PlaysIn and keep it with Bet. Then PlaysIn -> Bet -> Player. The UX could also be promoted to a PK and BetID could be dropped, making it similar to the above.

TABLE Player
  PK PlayerID
  PlayerName 

TABLE Bet
  -- Note: If PlaysIn needs PlayerID as well, use PK(PlayerId, BetName)
  --       and adjust the FK in PlaysIn
  PK BetID
  PlayerID
  BetName
  UX (PlayerID, BetName)

TABLE PlaysIn
  PK PlayID -- If you're gonna use Surrogates, be consistent
  BetID
  FK (BetID) -- Access to Player via Bet
  -- other things for a "Play"

Of course, TRIGGERS can "do it all", but are not represented directly in the relationships. Code can also be meticulous on inserts/updates - if you trust the DAL.

I would consider changing the model, as per the 2nd approach.

score 0 · Answer 3 · edited May 23 '17 at 12:05

0

Edit: As suggested by LoztInSpact, this solution really doesn't work. The problem is with transactions and the trigger not seeing uncommited changes. See here.

Try database triggers. You can check your condition and then either signal an error or change the inserted values to NULL or some special value.

CREATE TRIGGER check_bet_name BEFORE INSERT ON plays_in 
  FOR EACH ROW BEGIN
      DECLARE bet_exists INT DEFAULT 0;
      DECLARE msg VARCHAR(255);

      SELECT 1 INTO bet_exists 
        FROM Bet AS b1
        WHERE b1.BetID = NEW.BetID
          AND EXISTS (SELECT * 
            FROM plays_in AS p JOIN Bet AS b2 USING (BetID)
            WHERE p.PlayerID = NEW.PlayerID AND b2.BetName = b1.BetName
          )
        LIMIT 1;

      IF bet_exists THEN
        SET msg = "Bet name already exists...";
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
      END IF;
  END//

See also this answer.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 02 '13 at 00:40

Mifeet

12,949
5
60
108

I'd be very careful with this approach. Triggers to do cross row checks rarely work on most databases (not sure about MySQL) because they cannot read uncommitted rows from other transactions, i.e. they don't have the full picture. The best way is to structure the model correctly as per Catcall's suggestion. – LoztInSpace Jun 02 '13 at 06:35
@LoztInSpace I searched around and haven't found any support for your claim. Can you give some source? The official documentation for MySQL nor Oracle mention anything. This [question](http://stackoverflow.com/questions/460316/are-database-triggers-evil) summarizes elsewere - they mostly mention that they are not visible (for other programmers), but the [highest rated answer](http://stackoverflow.com/a/460343/2032064) specifically admits their usage relational integrity issues. – Mifeet Jun 02 '13 at 08:03
@LoztInSpace Oh my, I've just tested it and it turns out you are right! – Mifeet Jun 02 '13 at 08:39
In fact I had problems too in past with triggers on mySQL – Valentino Ru Jun 02 '13 at 10:50

MySQL: define uniqueness over multiple tables

3 Answers3