MySQL change column default to not null

Question

ALTER TABLE `pages` MODIFY `views` INT(11) NOT NULL DEFAULT 0

Trying to alter a column in a table which is currently allows NULL and has no default.

I want it to be NOT NULL and have a default of 0.

The Error message I get is

Invalid use of NULL value

Do you have data in table pages already? if so you may need to update pages set views to be 0 where views is null 1st then run your alter. Pretty sure default 0 applies to new rows, not existing. — xQbert
– xQbert, Commented Jan 27, 2017 at 21:27

Darshan Mehta · Accepted Answer · 2017-01-27 21:29:18Z

11

This is because the table already has a row or more with null value, you might need to update those to 0 before executing ALTER table, e.g.:

UPDATE test SET views = 0 WHERE views IS NULL;
ALTER TABLE test MODIFY COLUMN views int NOT NULL DEFAULT 0;

Here's the SQL Fiddle (commenting out update statement will result in the same error).

answered Jan 27, 2017 at 21:29

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1