CODEWITHSUNDEEP
mysqlsyntaxalter
zmf
zmf

Reputation: 9303

How do I modify a MySQL column to allow NULL?

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.

Upvotes: 535

Views: 594067

Answers (6)

Hmerman6006
Hmerman6006

Reputation: 1913

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.

Upvotes: 12

Jan Nejedly
Jan Nejedly

Reputation: 15

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

Upvotes: -12

Krishnrohit
Krishnrohit

Reputation: 599

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;

Upvotes: 49

Gerald Senarclens de Grancy
Gerald Senarclens de Grancy

Reputation: 7344

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

Upvotes: 8

ConroyP
ConroyP

Reputation: 41886

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

ALTER TABLE mytable MODIFY mycolumn varchar(255) null;

Upvotes: 363

Daniel Spiewak
Daniel Spiewak

Reputation: 55115

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.

Upvotes: 767

Related Questions