SQL Server : error creating table with multiple primary keys

Question

查询如下:

create TABLE tbl_temp (
[ref] numeric(18), 
[item_code] varchar(50), 
[item_desc] nvarchar(150),
[Qty] smallint) PRIMARY KEY (ref, item_code))

返回错误:

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'PRIMARY'.

Answer 1

试试这个方法:

create TABLE tbl_temp 
(
   [ ref] numeric(18), 
   [item_code] varchar(50), 
   [item_desc] nvarchar(150),
   [Qty] smallint,
   PRIMARY KEY (ref, item_code)
) 

但更好的方法是使用约束，如下所示:

create TABLE tbl_temp 
(
   [ ref] numeric(18), 
   [item_code] varchar(50), 
   [item_desc] nvarchar(150),
   [Qty] smallint,
   CONSTRAINT pk_tbl_temp  PRIMARY KEY (ref, item_code)
) 

或

create TABLE tbl_temp 
(
   [ ref] numeric(18), 
   [item_code] varchar(50), 
   [item_desc] nvarchar(150),
   [Qty] smallint
) 

ALTER TABLE tbl_temp 
 ADD CONSTRAINT pk_tbl_temp  PRIMARY KEY (ref, item_code)

这是更好的方法，因为你为你的 PK 设置了一个友好的名称。