View Full Version : How can I define two fields UNIQUE

05-08-2006, 01: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.

For Ex.
in a table products, fields category_id, subcategory_id must have unique combinations.

05-08-2006, 03:01 PM
Can you not validate this before you get to the database?

05-08-2006, 03:34 PM
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, 03: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.

05-08-2006, 07:40 PM
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.