CODEWITHSUNDEEP
mysqldatabase-designconstraints
Valentino Ru
Valentino Ru

Reputation: 5052

MySQL: define uniqueness over multiple tables

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?

Upvotes: 1

Views: 173

Answers (3)

Mifeet
Mifeet

Reputation: 13608

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.

Upvotes: 0

user2246674
user2246674

Reputation: 7709

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.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

Move "BetName" to the table "plays_in".

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

Then drop the table "Bet".

Upvotes: 4

Related Questions