correct usage of KEY, INDEX or UNIQUE

Question

I'm referring to this SO-question. Here, in the first answer, it's said that

UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.

As my english is not the best, I wonder what it means, that

the same row may not have identical non-NULL values for all columns in this index as another row

To quickly connect this with my acutal problem, here is my scenario. I have three tables

game(gameID, ...), PK(gameID)
player(gameID, playerName, ...), PK(gameID, playerName), FK(gameID)
prop(gameID, playerName, c1, c2, c3, ...) PK(gameID, playerName), FK(gameID, playerName)

Now, a player connected to a game can choose 1-n props. In such a prop, c1 - c3 are representing playing card values, more exact {1,2,3, ..., T, J, Q, K, A}.

Such a combination of three cards can consist of same values, but a combination can only be choosen once in a game. E.g. a player can choose {2,2,3} and {A,2,3}, but then, in that game, no other player can pick the same combination again.

Now to return on my preamble, the three elements c1, c2, c3 are not declared UNIQUE in the CREATE TABLE statement (otherwise the first combination in the example would not be possible). Does that mean I cannot use UNIQUE(gameID, c1, c2, c3) as a constraint to assure uniqueness of a combination in a game? Is this a good example to use INDEX(gameID, c1, c2, c3)? What is the difference in using the two variants in my particular case?

Finally the CREATE TABLE statement of that third table would be

create table prop(
    gameID  INTEGER NOT NULL,
    playerName  VARCHAR(25) NOT NULL,
    isBB    BOOLEAN NOT NULL DEFAULT FALSE,
    card1   VARCHAR(1) NOT NULL,
    card2   VARCHAR(1) NOT NULL,
    card3   VARCHAR(1) NOT NULL,
    PRIMARY KEY (gameID, playerName),
    FOREIGN KEY (gameID, playerName) REFERENCES player(gameID, playerName) ON DELETE CASCADE,
    UNIQUE(gameID, c1, c2, c3) /* OR INDEX(gameID, c1, c2, c3)?

)ENGINE=INNODB;

melihcelik · Accepted Answer · 2013-01-31T00:38:56.073

Indexes are used to speed up the searches in the database. Unique constraints on the other hand, enforce rules over the rows in the database (in your example, no two rows will have same values for 'gameID, c1, c2, c3' columns altogether.

You can have index on gameID to speed up queries like WHERE gameID = ?. But that doesn't guarantee that you'll have only 1 row having, let's say gameID = 1. If you need to have such a constraint, then UNIQUE constraint is what you need (for id fields, primary key is a better option since it combines uniqueness and indexing together).

To answer your specific questions:

Does that mean I cannot use UNIQUE(gameID, c1, c2, c3) as a constraint to assure uniqueness of a combination in a game?

If you do this then those combinations will be unique per game.

Is this a good example to use INDEX(gameID, c1, c2, c3)?

This is also a good example if you'll frequently search for combinations in a game.

correct usage of KEY, INDEX or UNIQUE

1 Answers1