Search code examples
mysqlstandards

What is the standard way to add DEFAULT constraint using ALTER TABLE query?

I want to change the default value of a column after creating the table in MySQL. Which one of the following queries is the standard way to change the default value of the column after creating the table?

  1. ALTER TABLE table_name 
MODIFY attribute_name DATATYPE DEFAULT default_value;

  2. ALTER TABLE table_name 
ALTER attribute_name SET DEFAULT default_value;
Solution

  • Either works.

    If you use MODIFY COLUMN, you must remember to include the column data type and other options that might be present on that column such as NOT NULL, AUTO_INCREMENT, or ZEROFILL. If you forget one of these options, it is removed from the column definition.

    This can have unintended side effects. For example, changing the default of a column is a metadata-only change, so it is instant even if the table is huge. But changing a column's nullability to or from NOT NULL requires a table restructure. So if you accidentally change the nullability of the column by leaving that option out, you find yourself waiting for hours when you didn't have to.

    If you use ALTER COLUMN ... SET DEFAULT, you don't have to spell out all those column options. They are left unaltered. This is more convenient and less error-prone if you only want to change the default value.

    As for which one is standard, the ALTER COLUMN .. SET DEFAULT is in the ANSI/ISO SQL specification. MODIFY COLUMN is a MySQL extension to the standard for the sake of Oracle compatibility.