MySQL と H2 の両方で動作するデータ定義言語 (DDL) のパターン集

Spring Boot のプロジェクトでマイグレーションツールとして Flyway を利用している。RDB として MySQL を利用しているのだけど、自動テストでは H2 を利用しているので、マイグレーションファイルのデータ定義言語 (DDL) は MySQL でも H2 でも動作するような SQL で記述する必要がある。意外と両方で動作するように書くのは手間だったので発見したパターンをメモっとく。

テーブル

テーブル追加

CREATE TABLE book (
  id    INTEGER      NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

テーブル名称変更

ALTER TABLE company
  RENAME TO publisher;

テーブル削除

DROP TABLE book;

カラム

カラム追加

ALTER TABLE book
  ADD COLUMN isbn VARCHAR(255) NOT NULL DEFAULT '';

カラム名称変更

ALTER TABLE author
  CHANGE family_name last_name VARCHAR(255) NOT NULL;

カラム定義変更

ALTER TABLE author
  MODIFY last_name VARCHAR(31) NOT NULL;

カラム削除

ALTER TABLE book
  DROP COLUMN isbn;

インデックス

インデックス追加

CREATE INDEX IX__author__first_name__last_name
  ON author (first_name, last_name);

インデックス削除

ALTER TABLE author
  DROP INDEX IX__author__first_name__last_name;

一意性制約

一意性制約追加（テーブル作成時）

CREATE TABLE book (
  id    INTEGER      NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  isbn  VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT UQ__book__isbn UNIQUE (isbn)
);

一意性制約追加

ALTER TABLE book
  ADD CONSTRAINT UQ__book__isbn UNIQUE (isbn);

一意性制約削除

ALTER TABLE book
  DROP INDEX UQ__book__isbn;

※ H2 データベースのバージョン 1.4.196 以前はサポートしていない。バージョン 1.4.197 以降でサポートされそう。

外部キー制約

外部キー制約追加（テーブル作成時）

CREATE TABLE book (
  id           INTEGER      NOT NULL AUTO_INCREMENT,
  title        VARCHAR(255) NOT NULL,
  publisher_id INTEGER      NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FK__book__publisher_id FOREIGN KEY (publisher_id) REFERENCES publisher (id)
);

外部キー制約追加

ALTER TABLE book
  ADD CONSTRAINT FK__book__publisher_id FOREIGN KEY (publisher_id) REFERENCES publisher (id);

外部キー制約削除

ALTER TABLE book
  DROP FOREIGN KEY FK__book__publisher_id;