MS SQL Server Replication - Auto identity field with check constraint

Author

Auto identity field with check constraint

marshallae@bowater.com

2005-12-12, 11:23 am

We are testing with the CTP Sept. version of SQL 2005 in hopes of
moving to the released version of SQL 2005. We have a table that has
an autoidentity range, with "Not For Replication" set to yes. (Is in a
merge-snapshot publication.) In SQL 2000, you used to be able to
uncheck the constraint "Enforce For inserts and Updates". Does anyone
know why you cannot do this in SQL 2005? This identity field
(location_ID) ss needed so that my users can add their own address
information concerning a prospect company, but I also need to insert
"real" location_IDs from existing companies. I try doing a SET
IDENTITY_INSERT ON, and try to insert but receive the below error:
It conflicted with an identity range check constraint

Does anyone know how I can work around this? (Is it resolved in the
released version of SQL 2005?)

Thanks,

Amy

Paul Ibison

2005-12-12, 11:23 am

Have you tried altering the check constraint directly: ALTER TABLE
cnst_example NOCHECK CONSTRAINT salary_cap. Resetting the current identity,
then reenabling the check constraint?
Rgds,
Paul Ibison

marshallae@bowater.com

2005-12-12, 1:23 pm

Paul Ibison wrote:
> Have you tried altering the check constraint directly: ALTER TABLE
> cnst_example NOCHECK CONSTRAINT salary_cap. Resetting the current identity,
> then reenabling the check constraint?
> Rgds,
> Paul Ibison

Paul, That works.. Thanks.. Do you know of any issues with doing this
if a user happens to replicate while this is happening? I insert data
in the morning, so chances are very slim.

Thanks for your help!

Amy

Paul Ibison

2005-12-12, 8:25 pm

It won't change the value of the identity attribute so things can't go wrong
that way. the potential issue with what you have is that the automatic range
management algorithm doesn't take account of your extra values when
allocating a new range once this one has been completed. Also I'd be
concerned that a subscriber may have this range itself and cause a PK
overlap.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

marshallae@bowater.com

2005-12-29, 11:23 am

Paul,

We won't have a primary key problem, because the initial identity
number was set way above any number I will be inputting into the table.

Thanks so much for your help!

Amy

Sponsored Links