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?

Of course not. Putting the salary in a. unique index has nothing to do with the constraint you want. — Gordon Linoff, Jan 28 '21 at 19:54
give it an unamed constraint (inline) or a unique index (inline) as here https://stackoverflow.com/a/17385085/73226 — Martin Smith, Jan 28 '21 at 20:00
This is an interesting requirement. You acknowledge that you could possibly hire two Martin Smiths but not if they were both born on the same day? — Aaron Bertrand, Jan 28 '21 at 20:02
@AaronBertrand This is a simplified example for easy explanation of the real problem I have. Yes, we cannot hire two people with same name and DOB, in this scenario. :-) — LCJ, Jan 28 '21 at 20:05
Ok, for future reference, simplifying your problem often comes at a cost. — Aaron Bertrand, Jan 28 '21 at 20:06

score 3 · Accepted Answer · answered Jan 28 '21 at 20:01

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

score 1 · Answer 2 · answered Jan 28 '21 at 20:00

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

Unique constraint on multiple columns in a table variable

2 Answers2