05-08-2006, 12:39 PM
How can I define two fields UNIQUE in MySql.
I want to create a table with constraint in with the combination of two fields must be UNIQUE.
in a table products, fields category_id, subcategory_id must have unique combinations.
05-08-2006, 02:01 PM
Can you not validate this before you get to the database?
Ideally, you'll do both.
Db-side to stop inconsistency, script-side to give useful messages to users.
ALTER TABLE tablename ADD CONSTRAINT 'name' UNIQUE(fieldname);
05-08-2006, 02:36 PM
But I think what he wants is to validate one field against another and he is trying to do it at database level using the unique feature.
I thing he will have to do this in PHP before he performs his insert/update query.
UNIQUE(field1,field2) will work.
re-reading the OP though, it doesn't really make sense. Why would (category,subcategory) want to be unique?
Perhaps you're after a CHECK, for example so that a product can't have it's category and subcategory being the same?
ALTER TABLE products ADD CONSTRAINT 'uniq_cat_chk' CHECK(categoryid<>subcategoryid);
Why a products table would have both category and subcategory fields is beyond me though.
And I still stick by having such forms of validation in both layers, it can make life so much easier when the database protects itself, rather than relying on code.