Search code examples
mysqldjangomysql-error-1064

mySQL DROP NOT NULL not working

I'm reading The definitive guide to django, and there's a line where i have to tell mySQL that a column can have a null value.

The book uses this 

ALTER TABLE books_book ALTER COLUMN publication_date DROP NOT NULL;

But when i run it i get this message :

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL' at line 1

What's going on ? I'm a beginner so i can't easily diagnose mySQL error.

Solution

  • You can only use DROP with column names, index names, and constraint names but not with a condition on data. I don't think NOT NULL is a named constraint and hence is the error.

    You can't use ALTER COLUMN to drop a column's NOT NULL definition but to DROP DEFAULT.

    Examples: 

    ALTER TABLE books_book ALTER COLUMN publication_date DROP DEFAULT;  
ALTER TABLE books_book ALTER COLUMN publication_date SET DEFAULT TODAY();

    If you are looking to remove the NOT NULL definition on the column, you should use either MODIFY or CHANGE syntax with ALTER TABLE.

    Examples: 

    ALTER TABLE books_book MODIFY COLUMN publication_date DATE NULL;  
ALTER TABLE books_book CHANGE COLUMN publication_date new_name DATE NULL;

    Refer To: ALTER TABLE Syntax.