How to Set Ignore_Dup_Key on for a Primary Key

Can I set ignore_dup_key on for a primary key?

It's not documented in Books Online, but I've found that while IGNORE_DUP_KEY is valid for Primary Keys, you can't change it with an ALTER INDEX; you'll have to drop and re-create the primary key.

Keep in mind that IGNORE_DUP_KEY doesn't allow you to actually store duplicate rows in a unique index, it simply changes how it fails when you try it:

ON: A warning message will occur when duplicate key values are inserted
into a unique index. Only the rows violating the uniqueness
constraint will fail.

OFF: An error message will occur when duplicate key values are inserted
into a unique index. The entire INSERT operation will be rolled
back.

From http://msdn.microsoft.com/en-us/library/ms175132.aspx

How to ignore duplicate Primary Key in SQL?

How do I make SQL ignore the duplicate primary keys?

Under no circumstances can a transaction be committed that results in a table containing two distinct rows with the same primary key. That is fundamental to the nature of a primary key. SQL Server's IGNORE_DUP_KEY option does not change that -- it merely affects how SQL Server handles the problem. (With the option turned on it silently refuses to insert rows having the same primary key as any existing row; otherwise, such an insertion attempt causes an error.)

You can address the situation either by dropping the primary key constraint or by adding one or more columns to the primary key to yield a composite key whose collective value is not duplicated. I don't see any good candidate columns for an expanded PK among those you described, though. If you drop the PK then it might make sense to add a synthetic, autogenerated PK column.

Also, if I have a row and I update the values of that row, is there any way to keep the original values of the row and insert a clone of that row below, with the updated values and creation/modification date updated automatically?

If you want to ensure that this happens automatically, however a row happens to be updated, then look into triggers. If you want a way to automate it, but you're willing to make the user ask for the behavior, then consider a stored procedure.

SQL type IGNORE_DUP_KEY

If there is no key or index on the column (as there isn't, in the statement you've given) then there already is no restriction on inserting the same value multiple times in a table.

DECLARE @i IdList
INSERT @i VALUES (1), (1), (1)

will work just fine. If you want to have a unique index with the IGNORE_DUP_KEY option so inserts will be discarded if the value is already there, rather than producing a constraint violation, you can do so by including a unique index with that option in the declaration:

CREATE TYPE [dbo].[IdList] AS TABLE (
    [Id] [int] NULL, 
    INDEX IX_IdList_Id UNIQUE(ID) WITH (IGNORE_DUP_KEY = ON)
);

Or with a primary key (for non-nullable columns):

CREATE TYPE [dbo].[IdList] AS TABLE ([Id] [int] PRIMARY KEY WITH (IGNORE_DUP_KEY = ON));

Be careful with this, because silently discarding duplicate values can be a real good way to mask essential problems in your processing. SQL Server does produce the informational message "Duplicate key was ignored", but that message is itself easy to ignore (and gives no details on what key(s)).

Why would you NOT set IGNORE_DUP_KEY to ON?

Whenever there is a deviation from the "normal" in the database , you probably want to know about it.

You kept the key unique because of some constraint arising out of business need that dictated it. The database is just keeping up it's side of the deal saying that 'hey you wanted this to be unique but now you are saying something contrary. Make up your mind'

If that is intentional you can ask database to shut up by using IGNORE_DUP_KEY :)

SQL Server ignore_dup_key on apparently ignored for merge statement

Your email column allows for null values. If you have a null value in target and a null value in source they will not be matched in the on expression. You will have a unique constraint exception when you are inserting the second null value.

Try this instead:

merge into dbo.email
using (
     select distinct email t from #t where email is not null
) p 
ON t = email
when not matched by target then
insert (email) values  (t);

Update:

Regarding ignore_dup_key you should read the remarks section in documentation for the merge statement:

If IGNORE_DUP_KEY is set to ON for any unique indexes on the target
table, MERGE ignores this setting.

To have a unique constraint in SQL Server that allows for multiple null values you should add a unique filtered index instead.

create unique index UX_xx on TableName(ColName) where ColName is not null

IGNORE_DUP_KEY in Sql Server 2000 with composite primary key

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.

(IGNORE_DUP_KEY = ON) or make an exclusive check when doing an INSERT?

Personally I think you should do three things:

1. Absolutely have the unique constraint to protect the data at its most basic level.
2. Check for potential violations before blindly inserting rows.
3. Wrap TRY/CATCH around the eventual insert to protect users from exceptions where the check fails.

For single-row inserts, this can be as simple as:

BEGIN TRY
  INSERT dbo.TEST(ValA, ValB, ValC, ValD)
    SELECT @ValA, @ValB, @ValC, @ValD
    WHERE NOT EXISTS
    (SELECT 1 FROM dbo.TEST
      WHERE ValA = @ValA AND ValB = @ValB AND ValC = @ValC AND ValD = @ValD);
END TRY
BEGIN CATCH
  PRINT 'Move along, nothing to see here...';
END CATCH

It gets a little more complicated if any or all of these columns are nullable.

For multi-row inserts, you can handle this in a variety of ways. You can have the entire batch fail if there is a non-unique value (either in the batch alone or conflicting with the table), or you can allow just the successful rows into the table. Situation A:

  IF EXISTS (SELECT 1 FROM @SourceOfMultipleRows AS r
    WHERE EXISTS (SELECT 1 FROM dbo.Test AS t WHERE t.ValA = r.ValA AND ...))
  OR EXISTS (SELECT 1 FROM @SourceOfMultipleRows
    GROUP BY ValA, ValB, ValC, ValD HAVING COUNT(*) > 1)
  BEGIN
    PRINT 'Not proceeding at all.';
  END
  ELSE
  BEGIN
    BEGIN TRY
      INSERT dbo.TEST(ValA, ValB, ValC, ValD)
        SELECT ValA, ValB, ValC, ValD
        FROM @SourceOfMultipleRows AS r
        WHERE NOT EXISTS (SELECT 1 FROM dbo.Test AS t
          WHERE t.ValA = r.ValA AND ...)
        GROUP BY ValA, ValB, ValC, ValD;
    END TRY
    BEGIN CATCH
      PRINT 'Move along, nothing to see here...';
    END CATCH
  END

Scenario B, where you want to keep the good rows and ignore duplicates:

BEGIN TRY
  INSERT dbo.TEST(ValA, ValB, ValC, ValD)
    SELECT ValA, ValB, ValC, ValD
    FROM @SourceOfMultipleRows AS r
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Test AS t
      WHERE t.ValA = r.ValA AND ...)
    GROUP BY ValA, ValB, ValC, ValD;
END TRY
BEGIN CATCH
  PRINT 'Move along...';
END CATCH

SQL Constraint IGNORE_DUP_KEY on Update

If I understand correctly, you want to do UPDATEs without specifying the necessary WHERE logic to avoid creating duplicates?

create table #t (col1 int not null, col2 int not null, primary key (col1, col2))

insert into #t 
select 1, 1 union all 
select 1, 2 union all 
select 2, 3

-- you want to do just this...
update #t set col2 = 1

-- ... but you really need to do this
update #t set col2 = 1
where not exists (
    select * from #t t2
    where #t.col1 = t2.col1 and col2 = 1
    )

The main options that come to mind are:

1. Use a complete UPDATE statement to avoid creating duplicates
2. Use an INSTEAD OF UPDATE trigger to 'intercept' the UPDATE and only do UPDATEs that won't create a duplicate
3. Use a row-by-row processing technique such as cursors and wrap each UPDATE in TRY...CATCH... or whatever the language's equivalent is

I don't think anyone can tell you which one is best, because it depends on what you're trying to do and what environment you're working in. But because row-by-row processing could potentially produce some false positives, I would try to stick with a set-based approach.

How is the sintaxis to use ignore_dup_key?

I've just edited this answer after @Alvaro Garcia Comment

insert into Table(IDA, IDB) 
SELECT T.IDA, T.IDB 
FROM (SELECT 1 as IDA, 2 as IDB) T
LEFT JOIN Table T2 ON T.IDA = T2.IDA AND T.IDB = T2.IDB
WHERE T2.IDA is null

before I was just checking existence and then insert.

---

Related Topics