How to use DROP DEFAULT in MySQL 8?

Question

I'm using MySQL: Server version: 8.0.36 Homebrew

I'm trying to remove the default value of a column (currently set to 0), and it's erroring:

ALTER TABLE comments ALTER COLUMN commentable_id integer DROP DEFAULT;

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 'integer DROP DEFAULT' at line 1

The above is the syntax i've seen everywhere according to a google search. I also tried this which i saw on the O'Reilly site: (note no 'COLUMN')

ALTER TABLE comments ALTER commentable_id integer DROP DEFAULT;

but that didn't work either.

If I set a default of null, then it worked:

ALTER TABLE comments MODIFY COLUMN commentable_id integer DEFAULT null;

and that is effectively the same, since my database's "default default" is null anyway. But, i'd still like to know why the first command didn't work. Is it a change in V8 perhaps?

Not sure what part of the O'Reilly site you found yourself on, but it's either outdated, a pretty glaring typo, or reference for a different DBMS than MySQL. — esqew
– esqew, Commented Dec 5, 2024 at 17:24

nbk · Accepted Answer · 2024-12-05 17:20:12Z

3

You have it almost right, but you can't use the column type, so it is just

alter table comments 
  alter column commentable_id drop default;

answered Dec 5, 2024 at 17:20

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

esqew Over a year ago

To add, modifying the datatype would require a syntax incompatible with this one (using MODIFY instead of ALTER [COLUMN], i.e.: ALTER TABLE comments MODIFY commentable_id integer). You'll necessarily need to perform these alterations in two separate queries. See the docs

Max Williams Over a year ago

@esqew Thanks - i wasn't trying to alter the datatype. I must have been unlucky with what I googled, the first one that I looked at (popsql.com/learn-sql/mysql/…) had the datatype, like in my question, and then I didn't notice that the second and third things i looked at didn't have that.

Collectives™ on Stack Overflow

How to use DROP DEFAULT in MySQL 8?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related