Mysql Question posted in
The official MySQL documentation can be found here.

How to use DROP DEFAULT in MySQL 8?

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    I would prefer using

    ALTER TABLE comments MODIFY commentable_id integer

    instead of

    ALTER TABLE comments ALTER COLUMN commentable_id DROP DEFAULT;

    When using the first method the table would have the following structure

    CREATE TABLE `comments` (
  `id` int DEFAULT NULL, ##### I added id for demonstration purposes
  `commentable_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

    Using the second method,

    CREATE TABLE `comments` (
  `id` int DEFAULT NULL,
  `commentable_id` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

    If I insert some data only on id column.

    For example

    INSERT INTO comments SET id = 2;

    With the first method I would have

    id  commentable_id
 1      null

    with the second one an error will be thrown ,

    Field ‘commentable_id’ doesn’t have a default value

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search