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.
2
Answers
You can add an
isFavorite
column in theItem
table. It’s value is eitherNULL
or a predefined value, like aENUM
value. Then you can add aUNIQUE
constraint over the two columnsplayerId
andisFavorite
. That way, a player can have only oneitem
as favorite, since multiple rows with the sameplayerId
andisFavorite
value would result in a unique constraint error. The table can look like this:Check the following queries how a new row would validate the unique constraint:
You can add a third table which links the
player
anditem
table for the favorite item for each player (if you don’t want a cycle reference betweenplayer
anditem
). There are two restrictions to solve:The first point can be solved with a multi-column foreign key. The table
item
needs an index for theplayerId
and theid
of the item. Then your new table for the favorite items can reference this index as a foreign key. The inclusion of theplayerId
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:The second point can be solved by a simple
UNIQUE
constraint on theplayerId
column of the new favorite items table. That way only one item can be marked as favorite. We adjust theCREATE TABLE
query for thefavItem
table as follow:See the following queries how they work:
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 iditemId=2
. The third row can’t be added either because the player with the idplayerId=1
cannot mark the item with the iditemId=1
(which he owns) as a favorite because of the UNIQUE constraint onplayerId
.