mysql 唯一 标识符,使用多列作为mysql的唯一标识符

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.

解决方案

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`)