SQL Server Unique Constraint in a Category According To FK

The below code works great for creatin开发者_运维问答g Unique Constraints :

ALTER TABLE <tablename> ADD CONSTRAINT
            <constraintname> UNIQUE NONCLUSTERED
    (
                <columnname>
    )

I have a table named Products which has FK for another table called Categories. Imagine that the below list is Categories :

Software

Movie

Music

What I need here is to have a unique constraint inside a category for my ProductName column inside Products table. For example, it is ok to have two same ProductName columns in a different category in Products table but not in the same category.

I am on SQL Server 2008 R2. Any thoughts?

You can add multiple columns into a unique constraint or unique index definition. You need a composite unique constraint with two columns ProductName, CategoryId

Example Syntax

ALTER TABLE Products ADD CONSTRAINT some_name 
      UNIQUE NONCLUSTERED(ProductName, CategoryId)