How do I modify a MySQL column to allow NULL?

mysql syntax alter

MySQL 5.0.45

What is the syntax to alter a table to allow a column to be null, alternately what's wrong with this:

ALTER mytable MODIFY mycolumn varchar(255) null;

I interpreted the manual as just run the above and it would recreate the column, this time allowing null. The server is telling me I have syntactical errors. I just don't see them.

column is not unique or anything else like that

Daniel Spiewak

You want the following:

ALTER TABLE mytable MODIFY mycolumn VARCHAR(255);

Columns are nullable by default. As long as the column is not declared UNIQUE or NOT NULL, there shouldn't be any problems.

There's an edge case which is the TIMESTAMP type, which depending on your MySQL version and config can be NOT NULL specifying NULL as suggested by @ConroyP is more correct.

This did not work for me! The column did not change. Maybe because I had a constraint to another table where the column was used (when not null).

ConroyP

Your syntax error is caused by a missing "table" in the query

ALTER TABLE mytable MODIFY mycolumn varchar(255) null;

This is actually the correct answer - whilst the NULL clause isn't required, there's nothing wrong with providing it. The missing TABLE from the ALTER TABLE statement was the real problem.

@SamStephens and Xofo: There is a primary and secondary ("alternately") question. This is the correct answer to the secondary question while the accepted answer is the correct answer to the primary one.

@SamStephens Just because there's nothing wrong with providing NULL, it doesn't make this answer any more "correct" than the accepted answer? Knowing that columns are nullable by default (as mentioned in the accepted answer) is helpful in regards to this particular question.

OP doesn't know why their statement didn't work. This answer explains why.

eeerahul

My solution:

ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL

For example:

ALTER TABLE SCHEDULE CHANGE date date DATETIME DEFAULT NULL;

Gerald Senarclens de Grancy

Under some circumstances (if you get "ERROR 1064 (42000): You have an error in your SQL syntax;...") you need to do

ALTER TABLE mytable MODIFY mytable.mycolumn varchar(255);

Hmerman6006

My solution is the same as @Krishnrohit:

ALTER TABLE `table` CHANGE `column_current_name` `new_column_name` DATETIME NULL;

I actually had the column set as NOT NULL but with the above query it was changed to NULL.

P.S. I know this an old thread but nobody seems to acknowledge that CHANGE is also correct.

Jan Nejedly

Use: ALTER TABLE mytable MODIFY mycolumn VARCHAR(255);

It would be better if your answer provided additional value on top of the other answers. In this case, your answer does not provide additional value, since Daniel Spiewak already posted that solution. If a previous answer was helpful to you, you should vote it up once you have enough reputation

How do I modify a MySQL column to allow NULL?

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Links

Contact US