i have a seven column in my mysql table 4 is A B C D if he already have value 1 ,2 ,3 ,4 then if anybody thing to re add him then table not allow this how i can do this
+1
A:
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.
Alec
2010-05-18 23:10:06
MySQL supports composite UNIQUE key constraints: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
OMG Ponies
2010-05-18 23:29:33
+3
A:
Why don't you simply make a unique key for A, B, C, D
ALTER TABLE <tablename> ADD UNIQUE KEY (A, B, C, D);
Sam
2010-05-18 23:13:33
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.
Alec
2010-05-18 23:25:08
@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...
OMG Ponies
2010-05-18 23:31:46
@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.
Sam
2010-05-18 23:35:11