To my surprise, the syntax for CREATE TABLE and ALTER TABLE does allow you to specify the IGNORE_DUP_KEY option for PRIMARY KEY and UNIQUE constraints.

Jamie Longstreet (11/5/2008)

---

The negative ("This option may NOT be specified...") - urrgh... semantics. Does it mean that it can be but that you shouldn't because it doesn't make sense but it is allowed anyway?[/hr]

Just the same Hugo - good question. It looks closely at an issue that is odd at best. Thanks

SanjayAttray (11/5/2008)

---

CREATE INDEX NotAllowed
ON Dest (Value)
WITH(IGNORE_DUP_KEY = ON);
go

Msg 1916, Level 16, State 4, Line 1
CREATE INDEX options nonunique and ignore_dup_key are mutually exclusive.


ignore_dup_key should come with Unique key.

CREATE UNIQUE INDEX NotAllowed

Good question though. I used this option mostly while merging data from different server.tables into my reporting server, where it won't insert duplicate data.

Anirban Paul (11/6/2008)

---

Good Questions Hugo...

skjoldtc (11/6/2008)

---

Good question. Not because of the question itself but because of one of the answers. I went on a tangent to relearn the goals of ACID. That reminded me of a design issue I'm currently working on. It's amazing how a question like this will have relevance when you least expect it.

Very timely.

Thanks.

:)