Auto identity field with check constraint

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

marshallae@bowater.com - 12 Dec 2005 16:15 GMT

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

Reply to this Message

Paul Ibison - 12 Dec 2005 16:35 GMT

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

Reply to this Message

marshallae@bowater.com - 12 Dec 2005 17:29 GMT

> 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

Reply to this Message

Paul Ibison - 12 Dec 2005 20:32 GMT

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)

Reply to this Message

marshallae@bowater.com - 29 Dec 2005 15:30 GMT

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

Reply to this Message