Use multiple columns as unique identifier for mysql

Question

I have a mysql table with the following columns:

group_id game_id user_id message last_update 

I want to make it so that no two rows can exist where the value of group_id for row x is equal to the value of group_id for row y AND the value of user_id for row x is also equal to the value of user_id for row y.

So, for example, let's say I insert the following following values:

group_id = 783 game_id = 34 user_id = 29237 message = none last_update = 11233452 

The above data, even if a mysql query tries to insert it, should not create a new row if a row already exists with the same combination of group_id and user_id. Is there a way to do this? Basically, I'm trying to get two columns to work together kind of like an unique index.

Answer 1

Yes, MySQL provides the ability to do this.

ALTER TABLE MyTable ADD UNIQUE KEY `my_unique_key` (`group_id`, `user_id`) 

I don't know what you're using this table for, but it sounds like this unique key might be a strong candidate for primary key of the table. A primary key is automatically a unique key as well. If you decide to make it the primary key, then do the following instead:

ALTER TABLE MyTable ADD PRIMARY KEY (`group_id`, `user_id`) 

(If you receive an error message that there is already a primary key, then issue ALTER TABLE MyTable DROP PRIMARY KEY and then repeat the above command.)

Edit: In response to user comment

You cannot have multiple rows with identical non-NULL values for the columns covered by the unique key. So you cannot have two rows where group_id = 0 AND user_id = 5, for example. 0 is a value. But if you make one or both of the columns nullable, you can have multiple rows that are identical up to positioning of NULLs. So you could have two (or more) rows where group_id IS NULL AND user_id = 1234.

Proviso: The above is true for both commonly-used MySQL storage engines (MyISAM and InnoDB). MySQL does have storage engines in which NULL is regarded as a unique value, but you are probably not using them.

If you make one or both columns nullable, then your unique key cannot be the primary key - a primary key has to be on columns that are NOT NULL.

Let's suppose you had made this key your primary key and you now want to allow NULL in the group_id column. I don't know what datatype group_id is at the moment; I'll assume it's currently INT UNSIGNED NOT NULL, but you will have to modify the below if it's not this. You would no longer be able to use this key as your primary key. Here is a command you could run to make the desired changes:

ALTER TABLE MyTable     DROP PRIMARY KEY,     MODIFY group_id INT UNSIGNED,     ADD UNIQUE KEY `my_unique_key_with_nulls` (`group_id`, `user`)