April 21, 2008 at 5:33 am
I was wondering about some behaviour in SQL when it comes to inserting duplicate keys...
I created a table in EM called Table1 (damn, I'm original) with 3 columns col1, col2 and col3 (keeps getting better :|)
All char(10) columns.
col1 = primary key
col2 = unique index, ignore duplicate keys UNCHECKED
col3 = unique index, ignore duplicate keys CHECKED
I then tried several insert statements to see the effect on a batch (I focussed on the 3rd select statement)
insert table1
select 'a', 'b', 'c' union all
select 'aa', 'bb', 'cc' union all
select 'aaa', 'bb', 'cc' union all
select 'aaaa', 'bbb', 'c' union all
select 'aaaaa', 'bbbbb', 'ccc'
--> results in 3 row inserts (1st, 2nd and 5th of the select statement) and an error message 'duplicate key ignored'
Then I tried this (only added 1 'c' to the 3rd select statement)
insert table1
select 'a', 'b', 'c' union all
select 'aa', 'bb', 'cc' union all
select 'aaa', 'bb', 'ccc' union all
select 'aaaa', 'bbb', 'c' union all
select 'aaaaa', 'bbbbb', 'ccc'
--> results in 0 row inserts and an error message 'Cannot insert duplicate key row in object 'Table1' with unique index 'IX_Table1_1'.
The statement has been terminated.'
Actually I thought that this was the message I was going to see in both batches because I though SQL would already fail on validating 'bb' in the first batch, 3rd statement...
Can someone shed some more light on this please?
Thx
July 4, 2008 at 11:08 am
Concering SQL Server 2005, if I get the documentation right the ignore duplicate key statement has no effect:
"The SQL Server 2005 Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON."
see
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d2297805-412b-47b5-aeeb-53388349a5b9.htm
The CHECKED constraint only pertains to foreign key indexes and has no relevance either.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply