none
Changing a column default value

    Question

  • Hi

    I'm using SQL Compact 4.0 to host a database and .Net 4.5 to access it.

    I'm trying to programatically change the default value of a column (one that already has a default value) and according to the documentation here...

    http://technet.microsoft.com/en-us/library/ms174123.aspx

    ... this sql query should do this for me:

    alter table [Table1] alter column [Col3] nvarchar(32) not null set default ('Test2')

    However, when I try to execute it, I get the following error:

    There was an error parsing the query. [Token line number = 1, Token line offset = 64, Token in error = set

    Can anyone see what I'm doing wrong here?

    Saturday, November 09, 2013 12:54 PM

Answers

  • Try omitting the column definition, specifying only the new default value:

    ALTER TABLE [Table1] ALTER COLUMN [Col3] SET DEFAULT 'Test2';


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 09, 2013 1:11 PM

All replies

  • Try omitting the column definition, specifying only the new default value:

    ALTER TABLE [Table1] ALTER COLUMN [Col3] SET DEFAULT 'Test2';


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 09, 2013 1:11 PM
  • Hi Dan,

    That was what I was looking for thanks.  Just to add, to change a default on a column that already had a default I had to do this:

    ALTER TABLE [Table1] ALTER COLUMN [Col3] DROP DEFAULT; ALTER TABLE [Table1] ALTER COLUMN [Col3] SET DEFAULT 'Test2';

    Saturday, November 09, 2013 1:46 PM