CODEWITHSUNDEEP
sqlsql-server
LCJ
LCJ

Reputation: 22652

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?

Upvotes: 2

Views: 1575

Answers (2)

Charlieface
Charlieface

Reputation: 71168

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

Upvotes: 4

BJones
BJones

Reputation: 2460

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

Upvotes: 1

Related Questions