522

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.

frederj
  • 1,483
  • 9
  • 20
zmf
  • 9,095
  • 2
  • 26
  • 28

6 Answers6

754

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.

Daniel Spiewak
  • 54,515
  • 14
  • 108
  • 120
  • 25
    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. – Matthew Buckett Jan 26 '16 at 13:37
  • 2
    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). – Rocologo Dec 18 '16 at 10:09
347

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

ALTER TABLE mytable MODIFY mycolumn varchar(255) null;
ConroyP
  • 40,958
  • 16
  • 80
  • 86
  • 43
    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 Nov 20 '14 at 20:38
  • @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. – jdunk Aug 11 '16 at 17:45
  • @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. – rybo111 May 11 '20 at 11:10
  • 1
    OP doesn't know why their statement didn't work. This answer explains why. – SamStephens May 12 '20 at 20:11
44

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;
eeerahul
  • 1,629
  • 4
  • 27
  • 38
Krishnrohit
  • 549
  • 4
  • 3
10

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.

Hmerman6006
  • 1,622
  • 1
  • 20
  • 45
8

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);
Gerald Senarclens de Grancy
  • 7,154
  • 7
  • 41
  • 55
-10

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

Jan Nejedly
  • 19
  • 5
  • 25
    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](http://stackoverflow.com/help/privileges/vote-up) once you have enough [reputation](http://stackoverflow.com/help/whats-reputation) – Luís Cruz Aug 12 '15 at 22:51