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 sure that there are no two lines where the group_id value for row x is equal to the group_id value for row y. And the value of user_id for string x is also equal to the value of user_id for string y.

So, let's say let's insert the following values:

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

The above data, even if the mysql query is trying to insert it, should not create a new row if the row already exists with the same combination of group_id and user_id. Is there any way to do this? Basically, I'm trying to get two columns to work together as a 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 are using this table for, but it looks like this unique key can be a strong candidate for the primary key of the table. The primary key is also automatically a unique key. If you decide to make it the primary key, do the following:

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

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

Edit: In response to user comment

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

Proviso: The above is true for commonly used MySQL storage systems (MyISAM and InnoDB). MySQL has storage mechanisms in which NULL treated as a unique value, but you probably don't use them.

If you make one or both columns null, then your unique key cannot be a primary key - the primary key must be in NOT NULL columns.

Suppose you make this key your primary key, and now you want to enable NULL in the group_id column. I do not know what data type group_id is at the moment; I assume this is now INT UNSIGNED NOT NULL , but you will have to change below if it is not. You can no longer use this key as the primary key. Here is the command you can run to make the necessary changes:

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

Answer 2

To do this, you can create the group_id and user_id compound keys using any mysql editor, such as phpmyadmin or sqlyog, and mark it as a unique index.