sql - 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?

question from:https://stackoverflow.com/questions/65944290/unique-constraint-on-multiple-columns-in-a-table-variable

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 (EmpName,DOB))