Menu
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.
743
Select Text option from the Formula Type drop down list; Then choose Extract cells with unique values (include the first duplicate) from the Choose a fromula list box; In the right Arguments input section, select a list of cells that you want to extract unique values.
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
UNIQUE constraint ensures that all values in a specific column is different. UNIQUE key does not allow duplicate values. UNIQUE key allows NULL values but does not allow NULL values multiple times. We can create multiple UNIQUE columns on one table however only one PRIMARY KEY for table.
We can define multiple Unique keys on a table where one or more columns combine to make a Unique key. According to ANSI, we can use multiple NULL values but in the SQL server, we can add only one NULL value.
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`) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
