CODEWITHSUNDEEP
mysql
user6885115
user6885115

Reputation:

MySQL change column default to not null

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

Upvotes: 7

Views: 10217

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

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).

Upvotes: 11

Related Questions