August 22, 2007 at 8:57 am
I have a table that we have been populating with data for a while now without problem. The primary key is of type uniqueidentifier and up until now has automatically generated the value whenever a new record is added.
Yesterday the table was edited and a new column added - now the primary key has stopped auto generating its unique value. When I look at the table in design mode I cannot edit the Identity settings because they are locked.
Does anyone know how to resolve this problem?
Thanks
August 22, 2007 at 9:45 am
I'm slightly confused because an identity column is a property of an int or bigint column - guids use newid() property.
if you allow edits on systems without release documentation and control you will have problems like this.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 22, 2007 at 11:24 am
You cannot just edit the identity setting for a column. You will need to rebuild the table for that. So basically when you added the column you could not have dropped the identity property of a column without dropping the column itself. However Colin is right that uniqueidentifier columns cannot be identity columns. Chances are good that during the table update a default has been dropped from the primary key column. You can add a (newid()) default to this pk column, and the values should be autogenerated once again.
Regards,
Andras
August 23, 2007 at 3:15 am
Colin/Andras,
Thank you both for your help. I added newid() to the default for the column and it is now working as expected.
Release documentation and control is something that we should do but unfortunately we haven't implemented anything like that yet. If you have any suggestions on what the format/content should be I would be extremely grateful.
Thanks
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply