SQL Oddity: ALTER TABLE and default values

Posted on by

So, the MySQL (and Drizzle) ALTER TABLE syntax allows you to easily change the default value of a column. For example:

CREATE TABLE t1 (answer int);
ALTER TABLE t1 ALTER answer SET DEFAULT 42;

So, you create a TIMESTAMP column and forgot to set the default value to CURRENT_TIMESTAMP. Easy, just ALTER TABLE:

create table t1 (a timestamp);
alter table t1 alter a set default CURRENT_TIMESTAMP;

(This is left as another exercise for the reader as to what this will do – again, maybe not what you expect)

3 thoughts on “SQL Oddity: ALTER TABLE and default values”

  2. This will work:

    CREATE TABLE t1 (a TIMESTAMP NULL);
    ALTER TABLE t1 CHANGE `a` `a` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL

    (and actually if you just

    CREATE TABLE t1 (a TIMESTAMP);

    … it will become (SHOW CREATE TABLE)

    CREATE TABLE `t1` (`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

    .. for the first TIMESTAMP in the table not explicitly declared NULLable. A MySQL ‘oddity’ but hard to remove as lots of applications depend on this.)

  3. For Timestamp column when we don’t specify default, its defaulted to DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

    “Change a a …” is an oddity which works :) You can use ALTER TABLE MODIFY!!
    But anyways as said above “parser error” using ALTER TABLE ALTER COLUMN is not working for TIMESTAMP. It works when default is not TIMESTAMP.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.