Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    May 2006
    Posts
    101
    Thanks
    0
    Thanked 1 Time in 1 Post

    How can I define two fields UNIQUE

    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.

  • #2
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you not validate this before you get to the database?

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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);

  • #4
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #5
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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?
    e.g.
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •