-1

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.

Rafael Adel
  • 7,673
  • 25
  • 77
  • 118

4 Answers4

1

Yes, this syntax is wrong.

If you only want to allow a column to sometimes contain null values, then it's already the default behavior.

Check if your table definition forbids nulls with

show create table

If it hasn't a default behavior, you will see UNIQUE and/or NOT NULL constraints.

mbarthelemy
  • 12,465
  • 4
  • 41
  • 43
1

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.

Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
0

The most minimalistic correct syntax is:

ALTER TABLE books_book ALTER COLUMN publication_date column_type NULL;

Replace column_type with the current type of publication_date, if you have any other specifiers for that column you should add in those as well. Supposing it's a date as the name suggests:

ALTER TABLE books_book ALTER COLUMN publication_date DATE NULL;

You can see how to recreate the column using mysqldump or from an administration interface by exporting the table (or as @mbarthelemy suggested with show create table books_book;), get the syntax for creating the column from there and and use it in alter table, but replace NOT NULL with NULL.

xception
  • 4,241
  • 1
  • 17
  • 27
0

This one works perfectly fine for MySQL 5.5

ALTER TABLE books_book CHANGE publication_date publication_date DATE;

bhushan_padhye
  • 21
  • 3