在存储过程中对表变量组合主键

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

我在ALTER TABLE @statistictemp上犯了一个错误。

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

不正确的语法“@statistictemp”附近。期望ID、QUOTED_ID或'.'。

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

我在这里做错了什么?如何向表变量添加复合主键?

1 个解决方案

#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.

Msg 2627，第14层，状态1，第13行违反主键约束“PK_#1EA48E8_B595483D208CD6FA”。不能在对象'dbo.@statistictemp'中插入重复键。声明已被终止。

#1