Date parsed: 19/10/2007 14:10:56
Date: Fri, 19 Oct 2007 12:10:56 +0200
usenet@tynemarch.co.uk wrote:
> I have a table in a database with a primary key and a unique candidate
> key
>
> PK int
> CK1 int
> CK2 int
> plus various data fields
>
> We change the rows that the record with one PK value gets the CK of
> another and vice versa
>
> e.g.. PK 1 CK 1 1 becomes PK 1 CK 1 2
> and PK 2 CK 1 2 becomes PK 2 CK 1 1
>
> Therefore there is no overall key violation except temporarily when
> the first record is saved but the second has not yet been changed. But
> SQL server says there is a constraint violation whilst saving, it
> cannot "look ahead" to the next SQL statement. We are saving the
> records in an transaction one at a time with a stored procedure but we
> have also tried a dataset. It's a large table so we don't want to
> delete and recreate the index.
>
> Is there anyway around it with SQL Server 2005 using VB.NET 2005 that
> would not impact hugely on performance?
>
if you can find value of CK that 100% sure will never exist.
Let's say it's -1 -1
- save PK1's CK in some variable(s)
- change PK1's row CK into -1 -1
- change PK2's row CK to 1 1 (from PK1's row)
- change PK1's row CK (fom variable value) to 1 2
ok?
