-1

I'm trying to run this change in SQL Server 2012 on the basis of Mantis and is presenting syntax error, but can not understand where I've tried to use even the SET command before the DEFAULT and the error is the same:

The default value of the column must be zero and can not be null.

Code:

ALTER TABLE mantis_user_pref_table 
    ALTER COLUMN redirect_delay INT DEFAULT 0 NOT NULL

erro

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Paulo Roberto
  • 1,498
  • 5
  • 19
  • 42

2 Answers2

2

You're trying to use incorrect syntax. This can't be done with one query in SQL Server, you have to add default constraint first and then set column to be not-nullable.

So you can do it something like:

ALTER TABLE mantis_user_pref_table 
    ADD CONSTRAINT DF_redirect_delay DEFAULT 0 FOR redirect_delay

UPDATE mantis_user_pref_table 
SET redirect_delay = 0 
WHERE redirect_delay IS NULL

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT NOT NULL
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
  • And before you can `ALTER` the column to be `NOT NULL`, you first need to make sure it contains no more `NULL` entries, since the default won't replace those with the default value if you just alter the column ... – marc_s Nov 01 '16 at 12:53
1

You can use this syntax when adding column:

ALTER TABLE mantis_user_pref_table 
    ADD redirect_delay INT NOT NULL 
    CONSTRAINT DF_mantis_user_pref_table__redirect_delay DEFAULT 0;

To add only constraint use:

ALTER TABLE mantis_user_pref_table 
    ADD CONSTRAINT DF_mantis_user_pref_table__redirect_delay DEFAULT ((0)) FOR redirect_delay
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ruslan K.
  • 1,912
  • 1
  • 15
  • 18