temporarily allow duplicate keys?

  • Thread starter Thread starter Steven Spits
  • Start date Start date
S

Steven Spits

Hi,

We have a case where there will be temporarly duplicate keys during the
update process. For example, Key "A" becomes "B" and "B" becomes "C". When
"A" is set to "B", we get a duplicate key error because "B" already exists
in the table.

Is there a way to temporarly disable all constraints at the beginning of a
batch update and enable it again when we're all done?

I've tried ALTER TABLE name NOCHECK CONSTRAINT ALL, but this only seems to
affect foreign keys.

Steven

- - -
 
Steven
If you have a PRIMARY KEY on the column , drop the constraint first and the
re-create -it. It does not make sence to play with the data on the column
that does not allow duplicates
 
strange that you would want to do this, but there are a number of ways.
Drop and recreate the key or play with the data in a temporary table first.
 
Steven said:
We have a case where there will be temporarly duplicate keys during the
update process. For example, Key "A" becomes "B" and "B" becomes "C". When
"A" is set to "B", we get a duplicate key error because "B" already exists
in the table.

Is there a way to temporarly disable all constraints at the beginning of a
batch update and enable it again when we're all done?

I've tried ALTER TABLE name NOCHECK CONSTRAINT ALL, but this only seems to
affect foreign keys.
Click to expand...

That's right, you cannot disable a primary key. Or more generally: you
cannot disable a unique index to be non-unique. (Actually, you can disable
an index in SQL 2005, but at the end you need to drop it and recreate it.)

The best strategy in my opinion would be to perform the process so that
duplicates does not occur. That is, first set B to C and then A to B.
First setting A to B sounds scary to me, since how do you know which B
to change to C?



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
Hey,
The best strategy in my opinion would be to perform the process so that
duplicates does not occur. That is, first set B to C and then A to B.
First setting A to B sounds scary to me, since how do you know which B
to change to C?
Click to expand...

You're absolutely right, must be my brain that's still in weekend-mode.

Thanks all!

Steven

- - -
 
i have a similar problem (i've already posted a message but just now
i' seen this one).

I want to make an insert from a DataTable to a SQL Database, i wan't
to ignore the error that brings when you try to insert a duplicate
key. I don't want to insert it anyway (ignore the primary key) i just
want to ignore the error mesagge, don't insert that value and continue
to keep inserting the other values.

Now i do a bulk copy to a temporary table and then insert de values
that are not duplicate with a SP. But i want to insert the data with a
single bulkcopy or insert command directly from .net

Thanks
 
Arnoled,

Set the property for that from the dataadapter. It is standard what you ask.

Cannot get the name of that property so I would have to search as you now on
msdn for that.

Cor
 
Sorry for the late response.

Yes, i did use a dataadater to make the insert. I set up an
InsertCommand and set de rows of the DataTable to Update or something
like that so they'd be inserted in the table. I made a SP that
inserted the items that weren't in the table (no duplicates).

Thanks
 
Back
Top