3

Due to some architectural reasons I have to ignore duplicate values on the index. It works perfectly well - except, when I am inserting wrong data. I am trying to insert value to the FK column that is supposed to throw:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__constrainName".
The conflict occurred in database, table "someTable", column 'FKColumn'.

Is there logic behind ignoring duplicate values that prevents insert statement from throwing this exception?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Falcon
  • 650
  • 1
  • 8
  • 24

1 Answers1

4

I assume you are talking about this kind of situation?

CREATE TABLE T2(T2_ID INT PRIMARY KEY)

INSERT INTO T2 VALUES (1),(2)

CREATE TABLE T1 (T1_ID INT, T2_ID INT REFERENCES T2)
CREATE UNIQUE CLUSTERED INDEX IX ON T1(T1_ID) WITH IGNORE_DUP_KEY

INSERT INTO T1 VALUES (1,2),(1,2),(2,3),(2,3)

/*FK Violation - No rows inserted*/
SELECT * 
FROM T1

/*Duplicate key violation and potential FK Violation - one row inserted*/
INSERT INTO T1 VALUES (1,2),(1,2),(1,3),(1,3)

SELECT * 
FROM T1

DROP TABLE T1
DROP TABLE T2

If a row is not inserted because it would cause a duplicate key violation then the FK constraint is not violated following the insert, hence no error.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845