Unique constraint on multiple columns in a table variable

Question

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?

Answer 1

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))

Answer 2

You can't have named constraints on table variables. Instead try:

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