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.
6 Answers
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.
Your syntax error is caused by a missing "table" in the query
ALTER TABLE mytable MODIFY mycolumn varchar(255) null;
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;
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);
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.
Use:
ALTER TABLE mytable MODIFY mycolumn VARCHAR(255);
