CURRENT_TIMESTAMPをTIMESTAMP型の列のデフォルト値に指定してみる

一見するとなんてことはない。
つまりは次のようなテーブル定義をするということ。

CREATE TABLE xxx(
      id INT NOT NULL
    , ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    , ts2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)

2つのタイムスタンプを必要とする場面としては、たとえば、ts1が登録日時でts2が最終更新日時なんていう場合が考えられる。
ここで、いくつかのDBMSで実際に次のようなテーブル定義をしてみる。

CREATE TABLE hoge(
      id INT NOT NULL
    , ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    , ts2 TIMESTAMP)

CREATE TABLE foo(
      id INT NOT NULL
    , ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    , ts2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

2つのテーブルの違いは、もちろんts2列に対するデフォルト値の違い。

これを実行してみると、次のDBMSでは2つともきちんと定義することができる。

• DB2 v9.1, v9.5/v9.7
• Derby v10.5.3.0
• H2 v1.2.6
• PostgreSQL v8.3/v8.4/v9.0
• SQLite v3.6.3

ちなみに、何らかの要因によって両方とも定義できなかったのは、次のとおり。

• Firebird v2.1/v2.5
  • "DEFAULT"のあたりで「token unknown」と言われる
• HSQLDB v1.8.1
  • "DEFAULT"のあたりで「unexpected token」と言われる
• Oracle 10g XE
  • 「ORA-00907: 右カッコがありません。」と言われる。追求するの面倒だったので放置(謎)

特異だったのがMySQL(MySQL v4.1/v5.0/v5.1)。
テーブルhogeの方は問題なく定義できるのだが、テーブルfooの方は次のようなエラーになる。

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

このことをある人(誰)から言われたときには、思わず「嘘っ！」と叫んでしまったらしい(謎)。

ちなみに、Microsoft SQL Server v9.0, v10.0 では、次のようなエラーメッセージが返され、上記のようなテーブルは定義できない。

テーブルには timestamp 列を 1 つだけ含めることができます。テーブル 'hoge1' には timestamp 列が既に 1 つあるので、列 'ts2' を追加できません。

Microsoft SQL Serverの場合は、TIMESTAMP型ではなくDATETIME型を使用する。

CREATE TABLE hoge2(
      id INT NOT NULL
    , ts1 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    , ts2 DATETIME)

CREATE TABLE foo2(
      id INT NOT NULL
    , ts1 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    , ts2 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)

こうすることで、ほかのDBMSと同様に扱うことができる。