MySQL constraint to ensure data integrity between two tables (1-n cardinality)-CodePudding

I made a minimal example to describe my problem using the concept of player using items in a game (which makes my problem easier to understand).

My problematic is the following. Let's say I have to store items and the player holding them in my database.

I didn't show it on the image but a player could hold no item but it's really important for my problem.

I translated that into :

Which gives the two following tables (with PK_ and #FK) :

In my example I can then fill them as such :

By doing :

Now, I want any player to have a "favorite" item, so I want to add a foreign key #item_id in the table Player but I want to make sure this new value refers to an item being hold by the right player in the Item table. How can I add a (check?) constraint to my table declarations so that condition is always true to ensure data integrity between my two tables? I don't think I can solve this by creating a third table since it's still a 1n cardinality. Also I don't want to ALTER the table I want to CREATE, since my database is not yet to be deployed.

CodePudding user response：

You can add an isFavorite column in the Item table. It's value is either NULL or a predefined value, like a ENUM value. Then you can add a UNIQUE constraint over the two columns playerId and isFavorite. That way, a player can have only one item as favorite, since multiple rows with the same playerId and isFavorite value would result in a unique constraint error. The table can look like this:

CREATE TABLE items(
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    playerId INT NOT NULL,
    isFavorite ENUM('FAV') NULL,
    CONSTRAINT items_UQ_fav UNIQUE (PlayerId, isFavorite)
);

Check the following queries how a new row would validate the unique constraint:

mysql> INSERT INTO
    items(playerId, isFavorite)
VALUES
    (4, NULL), (7, NULL), (7, 'FAV'), (5, 'FAV');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM items;
 ---- ---------- ------------ 
| id | playerId | isFavorite |
 ---- ---------- ------------ 
|  1 |        4 | NULL       |
|  4 |        5 | FAV        |
|  2 |        7 | NULL       |
|  3 |        7 | FAV        |
 ---- ---------- ------------ 
4 rows in set (0.00 sec)

mysql> INSERT INTO items(playerId, isFavorite) VALUES (5, 'FAV');
ERROR 1062 (23000): Duplicate entry '5-FAV' for key 'items.items_UQ_fav'

CodePudding user response：

You can add a third table which links the player and item table for the favorite item for each player (if you don't want a cycle reference between player and item). There are two restrictions to solve:

A player must own the item, they want to have as a favorite.
A player can have only one favorite item.

The first point can be solved with a multi-column foreign key. The table item needs an index for the playerId and the id of the item. Then your new table for the favorite items can reference this index as a foreign key. The inclusion of the playerId in the foreign key ensures that the item isn't "moved" to a different player while it is marked as favorite. The queries will look like this:

CREATE TABLE item(
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    playerId INT NOT NULL,
    INDEX item_id_and_playerId (playerId, id)
);

CREATE TABLE favItem(
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    playerId INT NOT NULL,
    itemId INT NOT NULL, 
    FOREIGN KEY favItem_FQ_id_and_playerId (playerId, itemId) REFERENCES item(playerId, id)
);

The second point can be solved by a simple UNIQUE constraint on the playerId column of the new favorite items table. That way only one item can be marked as favorite. We adjust the CREATE TABLE query for the favItem table as follow:

CREATE TABLE favItem(
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    playerId INT NOT NULL,
    itemId INT NOT NULL,
    FOREIGN KEY favItem_FQ_id_and_playerId (playerId, itemId) REFERENCES item(playerId, id),
    CONSTRAINT favItem_UQ_playerId UNIQUE (playerId)
);

See the following queries how they work:

mysql> SELECT * FROM player;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  3 |
 ---- 
3 rows in set (0.00 sec)

mysql> SELECT * FROM item;
 ---- ---------- 
| id | playerId |
 ---- ---------- 
|  1 |        1 |
|  2 |        1 |
|  3 |        2 |
|  4 |        3 |
|  5 |        3 |
|  6 |        3 |
 ---- ---------- 
6 rows in set (0.00 sec)

mysql> INSERT INTO favItem(playerId, itemId) VALUES (1, 2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO favItem(playerId, itemId) VALUES (3, 2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
    (`test`.`favItem`, CONSTRAINT `favItem_ibfk_1` FOREIGN KEY
    (`playerId`, `itemId`) REFERENCES `item` (`playerId`, `id`))
mysql> INSERT INTO favItem(playerId, itemId) VALUES (1, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'favItem.favItem_UQ_playerId'

The first row can be added without problem. However, the second row can't be added because the player with the id playerId=3 does not "own" the item with the id itemId=2. The third row can't be added either because the player with the id playerId=1 cannot mark the item with the id itemId=1 (which he owns) as a favorite because of the UNIQUE constraint on playerId.