Search code examples
sqlsql-server

Unique constraint on multiple columns in a table variable

I have a table variable in SQL Server 2016, as shown below.

DECLARE @Employee TABLE (EmpName VARCHAR(500), DOB DATE, AnnualSalary DECIMAL(10,2))

I need to make sure combination of EmpName and DOB is unique. Following will NOT work - it will say "Incorrect syntax".

CREATE UNIQUE INDEX IX_Temp ON @Employee (EmpName,DOB);

Since table variable doesn't allow named constraints, what is the best option to achieve this constraint?

Solution

  • We can declare a primary key inline

    DECLARE @Employee TABLE (
    EmpName VARCHAR(500), DOB DATE, AnnualSalary DECIMAL(10,2),
    PRIMARY KEY (EmpName,DOB))

    Or you can change PRIMARY KEY for UNIQUE if you want a non-primary key.

    You can also declare it as an index and give it a name in SQL Server 2016+:

    DECLARE @Employee TABLE (
    EmpName VARCHAR(500), DOB DATE, AnnualSalary DECIMAL(10,2),
    INDEX ix UNIQUE CLUSTERED (EmpName,DOB))