外部キー制約の定義順で DELETE が成功したり失敗したり

2013/04/16 追記

次のようなテーブル定義がありました。

CREATE TABLE t1
(
  a INT NOT NULL,
  PRIMARY KEY (a)
);

CREATE TABLE t2
(
  a INT NOT NULL,
  b INT NOT NULL,
  PRIMARY KEY (a, b)
);

CREATE TABLE t3
(
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  PRIMARY KEY (a, b, c)
);

/* (1) */
ALTER TABLE t2 ADD CONSTRAINT fk_t2_1 FOREIGN KEY (a)
  REFERENCES t1 (a)   ON DELETE CASCADE;

/* (2) */
ALTER TABLE t3 ADD CONSTRAINT fk_t3_1 FOREIGN KEY (a)
  REFERENCES t1 (a)   ON DELETE CASCADE;

/* (3) */
ALTER TABLE t3 ADD CONSTRAINT fk_t3_2 FOREIGN KEY (a,b)
  REFERENCES t2 (a,b) ON DELETE NO ACTION;

t1 t2 t3 は親子孫のような３階層のリレーションです。外部キー制約は次の目的で作成しています。

t1 の行を削除すると、その行を参照している t2 と t3 の行も一緒に削除される
→ 外部キー制約 (1) と (2)
t2 の行を参照する t3 の行がある場合は t2 の行を削除することは出来ない
→ 外部キー制約 (3)

この定義で次のように INSERT → DELETE を行うと・・・

INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1, 1);
INSERT INTO t3 VALUES (1, 1, 1);

DELETE FROM t1 WHERE a = 1;

外部キー制約 (1) と (2) によって全部消えて欲しいのですが、そうはならずにエラーになりました。

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `fk_t3_2` FOREIGN KEY (`a`, `b`) REFERENCES `t2` (`a`, `b`) ON DELETE NO ACTION)

が、外部キー制約の定義順を次のように変更すると・・・

/* (2) */
ALTER TABLE t3 ADD CONSTRAINT fk_t3_1 FOREIGN KEY (a)   REFERENCES t1 (a)   ON DELETE CASCADE;

/* (3) */
ALTER TABLE t3 ADD CONSTRAINT fk_t3_2 FOREIGN KEY (a,b) REFERENCES t2 (a,b) ON DELETE NO ACTION;

/* (1) */
ALTER TABLE t2 ADD CONSTRAINT fk_t2_1 FOREIGN KEY (a)   REFERENCES t1 (a)   ON DELETE CASCADE;

同じSQLを実行してもエラーにはならずに成功します。

外部キー制約の定義順にはきおつけないといけないですね。

そんなわけはない。

他の RDBMS だと遅延制約とかでどうにかできそうですが MySQL ではできません（MySQL は NO ACTION と RESTRICT が同じ意味、他の RDBMS だと NO ACTION を遅延制約にできることがある）。

１つのテーブルが複数の外部キー制約で参照されているときに、どの順番で ON DELETE が実行されるかなんて未定義なので（多分）、そんなものに依存した実装にするべきではありません。たまたま上手く動いてしまい、そのまま運用を続け、数カ月後や数年後になにかの拍子（テーブルを作りなおしたとか）で動かなくなると悲劇なので、↑のようなテーブル定義はするべきではないでしょう。

この例では (2) の外部キー制約が誤りで、代わりにトリガで同じ事をするか、アプリレイヤでどうにかするべきなのだと思います。

追記

親 -> 子 -> 孫の関係なら問題は判りやすいですが、次のようなひし形の関係だと問題はもっとメンドクサイかもしれません。

CREATE TABLE aa
(
    a_id INT NOT NULL,
    PRIMARY KEY (a_id)
);

CREATE TABLE bb
(
    b_id INT NOT NULL,
    a_id INT NOT NULL,
    PRIMARY KEY (b_id)
);

CREATE TABLE cc
(
    c_id INT NOT NULL,
    a_id INT NOT NULL,
    PRIMARY KEY (c_id)
);

CREATE TABLE dd
(
    d_id INT NOT NULL,
    b_id INT NOT NULL,
    c_id INT NOT NULL,
    PRIMARY KEY (d_id)
);

ALTER TABLE bb ADD CONSTRAINT fk_bb_1 FOREIGN KEY (a_id)
  REFERENCES aa (a_id) ON DELETE CASCADE;

ALTER TABLE cc ADD CONSTRAINT fk_cc_1 FOREIGN KEY (a_id)
  REFERENCES aa (a_id) ON DELETE CASCADE;

ALTER TABLE dd ADD CONSTRAINT fk_dd_1 FOREIGN KEY (b_id)
  REFERENCES bb (b_id) ON DELETE CASCADE;

ALTER TABLE dd ADD CONSTRAINT fk_dd_2 FOREIGN KEY (c_id)
  REFERENCES cc (c_id) ON DELETE NO ACTION;

INSERT INTO aa VALUES (1);
INSERT INTO bb VALUES (2, 1);
INSERT INTO cc VALUES (3, 1);
INSERT INTO dd VALUES (4, 2, 3);

DELETE FROM aa WHERE a_id = 1;

これも外部キー制約の定義順を入れ替えると成功したり失敗したりします。