Composite Primary Key On Table Variable In Stored Procedure

Question

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.

Leave a Comment Cancel reply