Search code examples
sql-serveruniqueunique-constraint

How to make "No Duplicates" column in SQL Server 2008?

I have a simple table in my SQL Server database. This table contains two columns: ID int, Name nvarchar(50). The ID column is the primary key for my table.

I want the "Name" column to be "(No Duplicates)", like in Microsoft Access, But this column isn't the primary column. How could I do this?

Solution

  • Add a unique constraint for that column:

    ALTER TABLE Foo ADD CONSTRAINT UQ_Name UNIQUE (Name)

    To add it through SQL Management Studio UI:

    1. Open SQL Server Management Studio.
    2. Expand the Tables folder of the database where you wish to create the constraint.
    3. Right-click the table where you wish to add the constraint and click Design.
    4. In Table Designer, click on Indexes/Keys.
    5. Click Add.
    6. Choose Unique Key in the Type drop-down list.

    To handle a situation where a unique constraint violation occurs, see for error 2601.