Looking to the MSDN help http://msdn2.microsoft.com/en-us/library/ms174123(SQL.100).aspx (Same topic I have in local MSDN Library) I could not understand why following commands did not work:
alter table [Albums] alter column [Comment] set default 'default';
alter table [Albums] alter column [Comment] set not null;
I was getting error message:
Incorrect syntax near the keyword 'set'.
First I found this solution for default:
alter table [Albums] add constraint DF_Albumns_Comment default 'default' for [Comment];
constraint name is optional;
For not null similar solution:
alter table [Albums] add constraint NN_Albumns_Comment check ([Comment] is not null);
produces slightly different result. This new constraint is added over existing table definitions. The definition for column [Comment] is that it still allows nulls.
But then I noticed that online help was in fact for SQL 2008 compact edition, and later I found updated version (February 2008) for SQL 2005
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
Now ther is no option for default here, so solution with new constraint should be used. But for not null constraint I can use this:
alter table [Albums] alter column [Comment] varchar(30) not null;
And no mistery.
Here I will post problems I and my colleagues met and solutions we found.
Tuesday, February 26, 2008
Alter column set default doesn't work in T-SQL 2005
Subscribe to:
Post Comments (Atom)
1 comment:
very helpful, I had the same problem, thanks!
Post a Comment