June 29, 2007 at 9:52 am
I am trying to add a new column with a default value to an existing table that has data using the management console but keep getting the following error:
'CMS_PAGE_GROUPS' table
- Error validating the default for column 'CMSGroupID'.
The column is data type uniqueidentifier and I want to use the new function newSequentialID() to generate default values for all the existing records and for new entries.
The thing is that I can write the following which works:
ALTER
TABLE CMS_PAGE_GROUPS ADD CMSGroupID uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID()
So why doesn't the management console let me? I am setting everything correctly as far as I know.
The reason I don't use the ALTER statement is that it will append the column to the bottom of the others whereas I want it at the top as its going to be a replacement Primary Key for an identity field. As far as I know you can't use an ALTER statement to change the positioning without creating a temp table, copying the values and then copying back again.
Its just seems strange that the management console won't let me do it. I have tried changing the position of the column, the datatype, the nullability, the case that newsequentialid() is written in but it all produces the same error.
Any help would be much appreciated.
June 29, 2007 at 10:04 am
quick and dirty, run your script in the query windows as you are doing, then do into SSMS right click > Design
drag you column to the position you want, click save. you will get a similar error message but click continue attempting to save. and walla your column is where u want it
June 29, 2007 at 11:44 am
Hi thanks for that trick. I didn't realise you can just click n drag columns around in the management console in 2005! I've never been able to do it before and always wanted to. Now I can just run the ALTER commands and drag the column to where I want. Great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply