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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum