SQLの制約エラーの詳細を取得する方法

SQLAP

1つ前のエントリで、SQLの制約はエラーの詳細拾えなくて使えないわーといったのが聞こえたわけではないのだろうが、Bruce Momijan(PostgreSQLのCore Developer)がこんなエントリを書いていた。

Matching Server and Client Constraints

  • チェック制約を使うと無効なデータの入力を避けることができるが、ユーザーに対するフィードバックにはあまり役に立たない。1つの改善案は、制約に名前をつけること
CREATE TABLE ledger (id SERIAL, balance NUMERIC(10,2)
                     CONSTRAINT "Zero and negative balances not allowed" CHECK (balance > 0));

INSERT INTO ledger VALUES (DEFAULT, -2.00);
ERROR:  new row for relation "ledger" violates check constraint "Zero and negative balances not allowed"
DETAIL:  Failing row contains (1, -2.00).

  • エラーメッセージを解析して制約名を取得することができる。けどまあそれでも色々問題あるけど

  • PostgreSQL 9.3からは、より簡単に制約名を取得することができるようになる

みたいなことが書かれているようだ。ドキュメントを見てみたところ、9.3ではPQresultErrorField()の引数に以下のものが追加されている。

  • PG_DIAG_SCHEMA_NAME
  • PG_DIAG_TABLE_NAME
  • PG_DIAG_COLUMN_NAME
  • PG_DIAG_DATATYPE_NAME
  • PG_DIAG_CONSTRAINT_NAME

ドライバが対応すれば、これらの値をクライアントから取得できるようになるだろう。

また、制約に名前をつけるというのはMySQLでも可能だ。MySQLはCHECK制約はサポートされていないので外部キー制約の例。

mysql> CREATE TABLE Bugs(
    ->   bug_id int NOT NULL,
    ->   reported_by bigint unsigned,
    ->   PRIMARY KEY (bug_id),
    ->   CONSTRAINT `報告者が変だよ！` FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
    -> );

Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into Bugs values (1, 1000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint 
fails (`sqlap`.`Bugs`, CONSTRAINT `報告者が変だよ！` FOREIGN KEY (`reported_by`) 
REFERENCES `Accounts` (`account_id`))

SQLアンチパターンメモ - iakioの日記