开发者

IGNORE_DUP_KEY in Sql Server 2000 with composite primary 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.

I've tried looking for this option in SQL Server Management Studi开发者_JAVA百科o 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:

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.

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


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:

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.

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜