I seven columns in my MySQL table. Four of these are called A B C D.
Assume that I already have values 1 ,2 ,3 ,4. How can I prevent a duplicate combination from being added.
Add a comment
|
3 Answers
Why don't you simply make a unique key for A, B, C, D
ALTER TABLE <tablename> ADD UNIQUE KEY (A, B, C, D);
4 Comments
Alec
I might be reading the question wrong, but this only works if you want a single A=1, A=2, etc; a single B=1, B=2, etc. However, if you want only unique combinations, like "A=1 and B=2 and C=3 and D=4", this won't work. Because then "A=1, B=2, C=3, D=4" would be different from "A=1, B=2, C=1, D=1", but it won't be allowed because there already was a B=2.
OMG Ponies
@Alec: A composite unique constraint means that all the values (four in this case) make up a distinct value. IE: if 1, 1, 1, 1 exists - the second attempt to add or update a row to 1,1,1,1 will return an error. But 1,2,1,1 and 1,1,2,1 would work...
Alec
@OMG Ponies, nice. Didn't know that was possible :)
Sam
@rich kid: you can enter that statement as it is for an existing table. It will fail if there are already multiple values for a,b,c,d. Then you will have to remove them first. @Alec: the combination of the 4 fields must be unique, not each individual field.
In MySql, you can create a unique key that is a combination of several columns.
ALTER TABLE <nameOfTable> ADD UNIQUE KEY (A, B, C, D);
However it should be noted that there could be a large performance impact if these fields are storing text or other long values.
Comments
You can set a MySQL field to UNIQUE, but not a combination of fields, as far as I know.
So, first do a simple query to see if the combination A=1, B=2, C=3, D=4 already exists. If not, add it, otherwise prompt the user with an error.
1 Comment
OMG Ponies
MySQL supports composite UNIQUE key constraints: dev.mysql.com/doc/refman/5.1/en/alter-table.html
