SQL 存储过程中表变量上的复合主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9323533/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Composite Primary Key On Table Variable In Stored Procedure
提问by Greg
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 @statistictempsaying
我在ALTER TABLE @statistictemp说时出错
Incorrect syntax near '@statistictemp'. Expecting ID, QUOTED_ID, or '.'.
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?
我在这里做错了什么？如何将复合主键添加到表变量？
回答by Steven Schroeder
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.
消息 2627，级别 14，状态 1，第 13 行
违反 PRIMARY KEY 约束“PK_ #1EA48E8_B595483D208CD6FA”。无法在对象“dbo.@statistictemp”中插入重复键。
该语句已终止。
