Failed to set default null value for column

Question

I already have a table, simplified example:

CREATE TABLE t (c INT NOT NULL);

And I need to change column default value to NULL, so I tried:

ALTER TABLE t ALTER COLUMN c SET DEFAULT NULL;

but I got the error "Error Code: 1067. Invalid default value for 'c'". It looks really strange, because query conforms with official docs.

I even tried to:

ALTER TABLE t ALTER COLUMN c DROP DEFAULT;

and after it to make a 'SET DEFAULT NULL' query, but the same error occurred. It's interesting, that query like:

ALTER TABLE t ALTER COLUMN c SET DEFAULT 1;

executed without errors.

I know, that it is possible to change column default value to NULL in my case using:

ALTER TABLE t MODIFY COLUMN c INT NULL;

but this query is really slow on big tables (it is much slower, than queries like 'SET DEFAULT 1')

So, how to just change default value to NULL?

I mean, without any overhead caused by 'MODIFY COLUMN' command.

Details: MySQL x64 version 5.7.10, Win8. Tested using MySQL Workbench.

You have created the table with Not Nullable column 'c', so you can't make it default as NULL. Without modifying the column to Nullable I'm afraid you can never set it to NULL.. Once you make it Nullable, it should be by default NULL when you add any new record without a value in this column. — ydoow
– ydoow, Commented Apr 18, 2016 at 4:04

Jan · Accepted Answer · 2016-04-18 04:03:31Z

3

By creating column as NOT NULL you have created a CONSTRAINT - declaring that values entered into that column may never be NULL.

A default value of NULL (set to null is value not present during INSERT) would create invalid data.

As sadly nullability constraint is part of the datatype in mysql the only way to make the column nullable will be

 ALTER TABLE t MODIFY COLUMN c INT NULL;

answered Apr 18, 2016 at 4:03

Jan

13.9k3 gold badges35 silver badges58 bronze badges

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

1 Answer 1