具有复合主键的Sql Server 2000中的IGNORE_DUP_KEY

I'm trying to create a table in SQL Server 2000, that has a composite primary key with IGNORE_DUP_KEY set to ON.

我正在尝试在SQL Server 2000中创建一个表,该表具有IGNORE_DUP_KEY设置为ON的复合主键。

I've tried looking for this option in SQL Server Management Studio Express but I couldn't find it so now I'm down to creating the table programatically. Every SQL command I found on Google or Stack Overflow gives me an error:

我已经尝试在SQL Server Management Studio Express中查找此选项,但我找不到它,所以现在我要以编程方式创建表。我在Google或Stack Overflow上找到的每个SQL命令都给出了一个错误:

Incorrect syntax near '('.

'('附近的语法不正确。

The table should have 4 columns (A,B,C,D) all decimal(18) and I need the primary key on A,B,C.

该表应该有4列(A,B,C,D)全十进制(18),我需要A,B,C上的主键。

I would appreciate if someone could post an example CREATE command.

如果有人可以发布一个示例CREATE命令,我将不胜感激。

1 个解决方案

#1

create table MyTable2 (
  [a] decimal(18,2) not null,
  [b] decimal(18,2) not null,
  [c] decimal(18,2) not null,
  [d] decimal(18,2),
  CONSTRAINT myPK PRIMARY KEY (a,b,c)
)

CREATE UNIQUE INDEX MyUniqueIgnoringDups
ON MyTable2 (a,b,c)
    WITH IGNORE_DUP_KEY       --SQL 2000 syntax
  --WITH(IGNORE_DUP_KEY = On) --SQL 2005+ syntax

--insert some data to test.
insert into mytable2 (a,b,c,d) values (1,2,3,4);--succeeds; inserts properly
insert into mytable2 (a,b,c,d) values (1,2,3,5);--insert fails, no err is raised.
-- "Duplicate key was ignored.   (0 row(s) affected)"

For anyone interested, here's an explanation of what's happening from Erland Sommarskog on the MSDN forums:

对于任何感兴趣的人,这里是对MSDN论坛上Erland Sommarskog发生的事情的解释:

When IGNORE_DUP_KEY is OFF, a duplicate key value causes an error and the entire statement is rolled back. That is, if the statement attempted to insert multiple rows, no rows are inserted.

当IGNORE_DUP_KEY为OFF时,重复的键值会导致错误并回滚整个语句。也就是说,如果语句试图插入多行,则不会插入任何行。

When IGNORE_DUP_KEY is ON, a duplicate key value is simply ignored. The statement completes successfully and any other rows are inserted.

当IGNORE_DUP_KEY为ON时,将忽略重复的键值。语句成功完成,并插入任何其他行。