duplicate rows and batch abort

  • 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

  • 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