Composite Primary Key On Table Variable In Stored Procedure

Question

I'm new to stored procedures and trying to add a composite primary key to a table variable.

DECLARE @statistictemp TABLE (     MajorName      VARCHAR(50) NOT NULL,      SubName       VARCHAR(50) NOT NULL,      DetailedName   VARCHAR(50) NOT NULL,      UniversityID   SMALLINT    NOT NULL,      StatisticValue DECIMAL(9,3) );  ALTER TABLE @statistictemp  ADD CONSTRAINT pk_statistictemp  PRIMARY KEY (MajorName, SubName, DetailedName, UniversityID); 

I'm getting an error on ALTER TABLE @statistictemp saying

Incorrect syntax near '@statistictemp'. Expecting ID, QUOTED_ID, or '.'.

What am I doing wrong here? How do you add a composite primary key to a table variable?

Answer 1

You can do it like this:

DECLARE @statistictemp TABLE (     MajorName       VARCHAR(50) NOT NULL,      SubName        VARCHAR(50) NOT NULL,      DetailedName    VARCHAR(50) NOT NULL,      UniversityID    SMALLINT NOT NULL,      StatisticValue  DECIMAL(9,3),     PRIMARY KEY (MajorName, SubName, DetailedName, UniversityID) ); 

You can test that the primary key constraint works by trying to insert duplicates: e.g.,

INSERT INTO @statistictemp(MajorName, SubName, DetailedName, UniversityID) SELECT 'a','b','c',1 INSERT INTO @statistictemp(MajorName, SubName, DetailedName, UniversityID) SELECT 'a','b','c',1 

The second statement will throw an error:

Msg 2627, Level 14, State 1, Line 13
Violation of PRIMARY KEY constraint 'PK_#1EA48E8_B595483D208CD6FA'. Cannot insert duplicate key in object 'dbo.@statistictemp'.
The statement has been terminated.