正しいDELETEが外部キー制約で拒否される理由と、PostgreSQLのSQLSTATEによる判別を確認します。
JavaでJDBCを学び始めたころ、DELETE文について私自身が戸惑ったことがあります。
例えば、次のようなコードです。
private static final String DELETE_SQL =
"DELETE FROM categories WHERE category_id = ?";
public int deleteCategory(int id)
throws SQLException {
try (Connection conn =
DatabaseConnection.getConnection();
PreparedStatement pstmt =
conn.prepareStatement(DELETE_SQL)) {
pstmt.setInt(1, id);
return pstmt.executeUpdate();
}
}
SQL文は単純です。
指定したIDのレコードがあれば削除する。
そしてexecuteUpdate()の戻り値を使えば、
int deletedRows =
categoryDAO.deleteCategory(id);
if (deletedRows > 0) {
System.out.println(
"分類が削除されました。");
} else {
System.out.println(
"指定されたIDの分類は"
+ "見つかりませんでした。");
}
のように、
削除できた → 1
対象がなかった → 0
と判定できそうです。
ところが、実際にデータベースアプリケーションを作っていると、
「DELETE文は正しい。指定したIDも存在する。それなのに削除できない」
という場面に出会います。
しかも、その場合はexecuteUpdate()が0を返すのではなく、SQLExceptionが発生することがあります。
私自身、初めてこの現象に出会ったときは、原因になかなかたどり着けませんでした。
Javaのコードを書いている最中だったため、頭の中がすっかりJava中心になっていて、deleteCategory()、PreparedStatement、executeUpdate()などを何度も見直しました。
しかし、原因はJavaコードの中にはありませんでした。
PostgreSQLのスキーマに自分で設定していた外部キー制約を、失念していたのです。
この経験も含めて、当時の私は次のような疑問を持ちました。
- DELETE文は間違っていないのに、なぜ削除できないのか
- 削除できないなら、なぜ
executeUpdate()が0を返さないのか SQLExceptionが発生したということは、Javaプログラムのバグなのか- PostgreSQLは何を守るためにDELETEを拒否したのか
SQLExceptionの中から、外部キー制約違反だけをどう見分けるのか- SQLSTATEの
23503とは何なのか catchした後に、なぜもう一度throw eすることがあるのか
この記事では、これらを順番に整理しながら、
PostgreSQLの外部キー制約違反をJava/JDBCからどのように扱うか
を見ていきます。
この記事の前提
この記事では、Java/JDBCからPostgreSQLを操作する場合を例にします。
後半では外部キー制約違反を判別するためにSQLSTATE
23503を使用します。これはPostgreSQLでforeign_key_violationに割り当てられているコードです。他のDBMSで同じ処理を書く場合は、そのDBMSとJDBCドライバのドキュメントを確認してください。
今回使う2つのテーブル
例として、簡単な出納帳を考えます。
分類を管理するcategoriesテーブルがあります。
categories
category_id | category_name
------------+--------------
1 | 給与
2 | 配当金
3 | 雑収入
4 | 食費
5 | 住居費
もう一つ、実際の収入や支出を記録するcash_entriesテーブルがあります。
cash_entriesには、その取引がどの分類に属するかを示すcategory_idがあります。
関係を簡略化すると、次のようになります。
categories
1
│
│ category_id
│
N
cash_entries
cash_entries.category_idは、categories.category_idを参照しています。
例えば、次のようなデータがあるとします。
categories
1 | 給与
4 | 食費
cash_entries
取引A | category_id = 1
取引B | category_id = 4
取引C | category_id = 4
つまり、
「給与」という分類を
取引Aが使用している
「食費」という分類を
取引B・取引Cが使用している
という状態です。
この参照関係を守るために、データベースには外部キー制約を設定できます。
今回の例では、概念的には次のような制約があります。
FOREIGN KEY (category_id)
REFERENCES categories(category_id)
ON DELETE RESTRICT
ON DELETE RESTRICTが指定されているため、ほかのデータから参照されている分類を先に削除することはできません。
IDが存在する「給与」を削除してみる
分類ID1の「給与」を削除するとします。
DELETE文は、
DELETE FROM categories
WHERE category_id = ?;
です。
Javaから、
pstmt.setInt(1, 1);
pstmt.executeUpdate();
を実行します。
ID1は確かに存在しています。
そのため、最初は、
削除成功
↓
executeUpdate() → 1
になると思うかもしれません。
ところが、「給与」はcash_entriesの取引から参照されています。
そのためPostgreSQLは削除を拒否します。
環境によってエラーメッセージの表示言語や細かな文言は異なりますが、例えば次のような外部キー制約違反になります。
org.postgresql.util.PSQLException: ERROR: ...
... violates foreign key constraint ...
ここで重要なのは、
「DELETE文の文法が間違っているから失敗したわけではない」
ということです。
なぜデータベースは削除を拒否するのか
もしPostgreSQLが「給与」の削除を許可したら、どうなるでしょうか。
削除前は、
categories
1 | 給与
cash_entries
取引A | category_id = 1
です。
ここでcategoriesのID1だけを削除すると、
categories
ID=1 が存在しない
のに、
cash_entries
取引A | category_id = 1
だけが残ってしまいます。
すると、
「取引Aのcategory_id = 1は、いったい何の分類なのか？」
が分からなくなります。
つまり、データの参照関係が壊れます。
そこでデータベースは、
この分類は別のデータから参照されている
↓
今削除すると参照関係が壊れる
↓
削除を拒否する
という判断をします。
外部キー制約は、DELETEを邪魔するための仕組みではありません。
データ同士の整合性を守るための仕組みです。
原因がJavaコードの中にあるとは限らない
ここで、私自身がこの問題に最初に遭遇したときの失敗をもう少し振り返ってみます。
当時、私はJava側の削除処理を書いていました。
そのため、削除できない原因を探すときも、自然にJavaコードばかりを見ていました。
例えば、
deleteCategory()の書き方が間違っているのではないかPreparedStatementの使い方がおかしいのではないかexecuteUpdate()の戻り値について勘違いしているのではないか- プレースホルダへ設定しているIDが間違っているのではないか
- DELETE文そのものに問題があるのではないか
といった点です。
しかし、いくらJavaコードやDELETE文を確認しても、明らかな間違いは見つかりません。
原因は、そのさらに先にありました。
「PostgreSQL側には、どのような制約を設定していたか？」
という視点が抜けていたのです。
今回のアプリケーションでは、PostgreSQLのスキーマに外部キー制約を設定していました。
Java
↓
JDBC
↓
SQL
↓
PostgreSQL
↓
テーブル定義・制約・現在のデータ
Javaから見ると、実行しているのは、
pstmt.executeUpdate();
という1行です。
しかし、そのSQLを実際に受け取ったPostgreSQLは、単にDELETE文の文法だけを見ているわけではありません。
テーブル定義や外部キー制約、そして現在格納されているデータの参照関係も確認したうえで、削除してよいかを判断しています。
データベースアプリケーションでは、プログラムの動作を決めるものはJavaコードだけではありません。
Javaコード
+
JDBC
+
SQL
+
テーブル定義
+
PRIMARY KEY / FOREIGN KEY / UNIQUE / NOT NULL / CHECK
+
現在のデータ
が組み合わさって動作します。
そのため、JavaコードにもSQL文にも原因が見つからないときは、
「原因は本当にJava側にあるのか？」
と一度視点を切り替えることが大切です。
特にデータベース更新時のエラーでは、DB側のスキーマ、制約、データの状態まで確認することが、原因究明への近道になる場合があります。
「エラーが出た＝プログラムのバグ」とは限らない
JDBCを学び始めたころ、スタックトレースが表示されると、
「Javaプログラムのどこかを間違えたのではないか」
と考えがちでした。
しかし、今回のケースでは、
Javaコードの文法ミス
×
DELETE文の文法ミス
×
PostgreSQLの異常
×
外部キー制約が正しく働いた
○
です。
SQLExceptionが発生したこと自体は、Javaプログラムが壊れていることを意味しません。
今回の場合は、
「そのDELETEを実行するとデータの整合性が壊れるので、PostgreSQLが正しく拒否した」
という結果です。
データベースアプリケーションを学ぶうえで、これは重要な感覚だと思います。
「削除できない」には種類がある
ここで、DELETEの結果を整理してみます。
1. 対象が存在し、削除できる
例えば、ほかのデータから参照されていない分類を削除します。
対象あり
参照されていない
↓
DELETE成功
↓
executeUpdate() → 1
2. 対象が存在しない
例えば、ID999の分類が存在しないとします。
対象なし
↓
DELETE文そのものは正常終了
↓
削除された行は0件
↓
executeUpdate() → 0
3. 対象は存在するが、外部キー制約により削除できない
今回の「給与」です。
対象あり
↓
ほかのデータから参照されている
↓
PostgreSQLがDELETEを拒否
↓
SQLException
この違いが重要です。
対象がない
ことと、
対象はあるが、制約により削除できない
ことは、同じではありません。
だから、外部キー制約違反では単純に0が返るのではなく、SQLの実行がエラーとして扱われます。
Java側ではSQLExceptionとして受け取る
DAOの削除メソッドは、例えば次のようになっています。
public int deleteCategory(int id)
throws SQLException {
try (Connection conn =
DatabaseConnection.getConnection();
PreparedStatement pstmt =
conn.prepareStatement(DELETE_SQL)) {
pstmt.setInt(1, id);
return pstmt.executeUpdate();
}
}
通常なら、
return pstmt.executeUpdate();
から、
1
または、
0
が返ります。
しかし、PostgreSQLで外部キー制約違反が発生すると、正常な戻り値を返すところまで処理が進みません。
流れは、
Java
│
│ DELETE
↓
PostgreSQL
│
│ 外部キー制約を確認
↓
削除を拒否
│
↓
PostgreSQL JDBC Driver
│
↓
SQLException
│
↓
Java
となります。
そこで呼び出し側では、SQLExceptionを処理する必要があります。
すべてのSQLExceptionを「外部キー制約違反」と考えてよい？
ここで次の問題が出てきます。
例えば、
try {
categoryDAO.deleteCategory(id);
} catch (SQLException e) {
System.err.println(
"この分類は使用中なので"
+ "削除できません。");
}
と書けば、一見うまくいきそうです。
しかし、SQLExceptionが発生する原因は外部キー制約違反だけではありません。
例えば、
データベースへの接続失敗
SQL文の誤り
存在しないテーブル
存在しないカラム
各種制約違反
その他のデータベースエラー
などがあります。
もしすべてを、
この分類は使用中なので削除できません
と表示してしまったら、実際には接続障害なのに、利用者には外部キー制約違反だと誤って伝えてしまいます。
そこで、
発生したSQLExceptionが何のエラーなのかを判別する
必要があります。
SQLSTATEとは
JDBCのSQLExceptionには、
e.getSQLState()
というメソッドがあります。
SQLSTATEは、データベースエラーの種類を識別するための5文字のコードです。
例えば今回のPostgreSQLでは、外部キー制約違反に、
23503
が割り当てられています。
PostgreSQLのエラーコード表では、
23503 → foreign_key_violation
です。
Javaでは、
String sqlState =
e.getSQLState();
で取得できます。
つまり、
SQLException
↓
getSQLState()
↓
"23503"
↓
PostgreSQLの外部キー制約違反
と判別できます。
この記事ではPostgreSQLを前提にする
ここは特に注意したいところです。
この記事で使う、
"23503"
は、PostgreSQLで外部キー制約違反を判別するために使っています。
SQLSTATEには標準化されたコード体系がありますが、DBMSによる実装やベンダー固有の扱いもあります。
したがって、
if ("23503".equals(e.getSQLState())) {
というコードを、
「どのDBMSでも必ず同じように使えるコード」
として覚えるのは避けた方がよいでしょう。
この記事では、
Java/JDBC
+
PostgreSQL
という前提で説明しています。
他のDBMSを使う場合は、そのDBMSとJDBCドライバのドキュメントを確認してください。
getErrorCode()との違い
SQLExceptionには、
e.getErrorCode()
というメソッドもあります。
こちらはJDBC API上、データベースベンダー固有のエラーコードを取得するためのものです。
一方、
e.getSQLState()
はSQLStateを取得します。
今回の記事では、PostgreSQLが外部キー制約違反に割り当てているSQLSTATE 23503を使って判別します。
getSQLState()
↓
SQLSTATE
↓
今回: "23503"
getErrorCode()
↓
ベンダー固有のエラーコード
この2つを混同しないようにしておくとよいでしょう。
PostgreSQLの外部キー制約違反だけを判別する
定数として、
// PostgreSQL: foreign_key_violation
private static final String FK_VIOLATION_STATE =
"23503";
を用意しておきます。
そして、SQLExceptionを捕捉したときに、
if (FK_VIOLATION_STATE.equals(
e.getSQLState())) {
...
}
と判定します。
文字列を比較するとき、
e.getSQLState().equals(
FK_VIOLATION_STATE)
ではなく、
FK_VIOLATION_STATE.equals(
e.getSQLState())
としておけば、仮にgetSQLState()がnullを返すケースでも、この比較自体でNullPointerExceptionになるのを避けられます。
実際の削除処理
例えば、呼び出し側の削除処理を次のようにします。
// PostgreSQL: foreign_key_violation
private static final String FK_VIOLATION_STATE =
"23503";
private static void deleteCategory()
throws SQLException {
try {
System.out.print(
"削除する分類のIDを入力してください: ");
int id =
Integer.parseInt(
scanner.nextLine());
int deletedRows =
categoryDAO.deleteCategory(id);
if (deletedRows > 0) {
System.out.println(
"分類が削除されました。");
} else {
System.out.println(
"指定されたIDの分類は"
+ "見つかりませんでした。");
}
} catch (NumberFormatException e) {
System.err.println(
"!! エラー: IDは半角数字で"
+ "入力してください。");
} catch (SQLException e) {
if (FK_VIOLATION_STATE.equals(
e.getSQLState())) {
System.err.println(
"!! エラー: この分類は"
+ "出納帳データで使用されているため、"
+ "削除できません。");
return;
}
throw e;
}
}
このコードでは、異なる種類の失敗を分けて扱っています。
処理を3つに分けて読む
数字として入力できない
catch (NumberFormatException e) {
は、Java側の入力値の問題です。
入力: abc
↓
Integer.parseInt()
↓
NumberFormatException
これはデータベースへDELETEを送る前に分かります。
PostgreSQLの外部キー制約違反
if (FK_VIOLATION_STATE.equals(
e.getSQLState())) {
は、データベースが参照関係を守った結果です。
IDは正しい
↓
DELETEをPostgreSQLへ送信
↓
参照中の分類だった
↓
SQLSTATE 23503
↓
利用者向けメッセージ
それ以外のSQLException
最後に、
throw e;
があります。
ここも、初めて見たときに疑問を持ちやすい部分です。
catchしたのに、なぜthrow eするのか？
通常、
catch (SQLException e) {
と書いたら、
「ここで例外処理が終わる」
ように感じるかもしれません。
しかし今回、このcatchで特別に処理したいのは、
PostgreSQLの外部キー制約違反
SQLSTATE = 23503
だけです。
例えば、データベース接続そのものに失敗した場合まで、
この分類は使用されているため削除できません
と表示してはいけません。
そこで、
if (FK_VIOLATION_STATE.equals(
e.getSQLState())) {
// このエラーは意味が分かっている
// 利用者向けに処理する
...
return;
}
とします。
そして、それ以外は、
throw e;
で上位へ渡します。
流れとしては、
SQLException
↓
SQLSTATEは23503？
／ ＼
Yes No
↓ ↓
外部キー 原因をここで
制約違反 決めつけない
↓ ↓
利用者向け throw e
メッセージ ↓
上位へ
です。
これは、
「分かっているエラーだけをここで処理し、分からないエラーを勝手につぶさない」
ための実装です。
throw eの先ではどうなる？
例えばdeleteCategory()の呼び出し元で、より一般的なデータベースエラーとして処理できます。
try {
deleteCategory();
} catch (SQLException e) {
System.err.println(
"データベース操作中に"
+ "エラーが発生しました。");
e.printStackTrace();
}
すると、
外部キー制約違反
↓
deleteCategory()で処理
↓
利用者向けメッセージ
それ以外のSQLException
↓
throw e
↓
上位で一般的なDBエラーとして処理
と役割を分けられます。
入力値検証と外部キー制約は役割が違う
今回の例には、もう一つ大切なポイントがあります。
Java側では、
Integer.parseInt(...)
などを使って、利用者の入力値を確認できます。
例えば、
abc
をIDとして入力したら、Java側で弾けます。
しかし、
ID = 1
そのものは、整数としては正しい入力です。
そのIDが、
ほかの取引データから参照されているか
は、単純な文字列や数値の入力チェックとは別の問題です。
整理すると、
"abc"をIDとして入力
↓
Java側で判断できる
↓
入力値検証
ID=1を削除
しかし取引から参照中
↓
データ同士の関係の問題
↓
データベースの外部キー制約
です。
Java側の入力値検証と、データベース側の制約は、どちらか一方だけあればよいというものではありません。
それぞれ守っているものが違います。
参照されていない分類なら削除できる
外部キー制約があるからといって、categoriesのデータが何も削除できなくなるわけではありません。
例えば、新しく、
9 | 削除確認用
という分類を登録したとします。
まだcash_entriesから一度も参照されていないとします。
その状態で、
DELETE FROM categories
WHERE category_id = 9;
を実行すれば、削除できます。
その場合は、
executeUpdate() → 1
となります。
つまり、外部キー制約は、
すべてのDELETEを禁止する
ものではありません。
参照関係を壊すDELETEだけを拒否する
仕組みです。
3つの結果を並べると理解しやすい
ここまでを並べると、DELETEの結果は次の3種類に整理できます。
参照されていない既存ID
対象あり
参照なし
↓
DELETE成功
↓
executeUpdate() → 1
存在しないID
対象なし
↓
DELETEは正常終了
↓
executeUpdate() → 0
参照されている既存ID
対象あり
参照あり
↓
PostgreSQLが削除を拒否
↓
SQLException
↓
getSQLState()
↓
"23503"
「削除できなかった」という結果だけを見ると似ていますが、
0件だった
のか、
制約違反でSQLが失敗した
のかでは意味がまったく違います。
エラーメッセージの文字列ではなくSQLSTATEを見る理由
外部キー制約違反が発生すると、PostgreSQLからエラーメッセージも返されます。
しかし、エラーメッセージの文章そのものを、
if (e.getMessage().contains(
"violates foreign key constraint")) {
のように比較する方法は避けたいところです。
エラーメッセージは、
- PostgreSQLの設定
- 表示言語
- ドライバやバージョン
- 制約名
- 詳細情報
などによって変化する可能性があります。
一方、PostgreSQLでは外部キー制約違反にSQLSTATE 23503が割り当てられています。
そのため、
"23503".equals(e.getSQLState())
のようにエラーの種類を示すコードで判定する方が、目的が明確です。
PostgreSQLでは23503はforeign_key_violation
PostgreSQL公式ドキュメントのエラーコード一覧では、Class 23がIntegrity Constraint Violation、つまり整合性制約違反です。
その中に、
23502 → not_null_violation
23503 → foreign_key_violation
23505 → unique_violation
23514 → check_violation
があります。
今回使っている、
23503
は、このうちのforeign_key_violationです。
同じSQLExceptionでも、SQLSTATEを見ることでエラーの種類をより具体的に判別できます。
完成した処理をもう一度見る
最後に、今回の中心となる部分をもう一度見てみます。
// PostgreSQL: foreign_key_violation
private static final String FK_VIOLATION_STATE =
"23503";
private static void deleteCategory()
throws SQLException {
try {
System.out.print(
"削除する分類のIDを入力してください: ");
int id =
Integer.parseInt(
scanner.nextLine());
int deletedRows =
categoryDAO.deleteCategory(id);
if (deletedRows > 0) {
System.out.println(
"分類が削除されました。");
} else {
System.out.println(
"指定されたIDの分類は"
+ "見つかりませんでした。");
}
} catch (NumberFormatException e) {
System.err.println(
"!! エラー: IDは半角数字で"
+ "入力してください。");
} catch (SQLException e) {
if (FK_VIOLATION_STATE.equals(
e.getSQLState())) {
System.err.println(
"!! エラー: この分類は"
+ "出納帳データで使用されているため、"
+ "削除できません。");
return;
}
throw e;
}
}
処理を言葉で表すと、
IDを入力
↓
数値として正しい？
├─ No → 入力エラー
│
└─ Yes
↓
DELETE
↓
正常終了？
／ ＼
Yes No
↓ ↓
1または0 SQLException
↓
SQLSTATE = 23503？
／ ＼
Yes No
↓ ↓
外部キー制約 throw e
違反として処理 ↓
上位で処理
となります。
まとめ
DELETE文が正しいのに削除できない場合、必ずしもJavaコードやSQL文にバグがあるとは限りません。
今回の例では、
削除しようとした分類
↓
ほかの取引から参照されている
↓
削除すると参照関係が壊れる
↓
PostgreSQLが外部キー制約で拒否
↓
JDBCではSQLException
↓
SQLSTATE = 23503
という流れでした。
特に覚えておきたいのは、次の3つの違いです。
削除成功
→ executeUpdate() = 1
対象なし
→ executeUpdate() = 0
外部キー制約違反
→ SQLException
→ PostgreSQLではSQLSTATE 23503
また、SQLExceptionをすべて同じエラーとして扱うのではなく、
e.getSQLState()
を使って、自分が意味を理解して処理できるエラーだけを判別することも重要です。
そして、それ以外のSQLExceptionは、
throw e;
で上位へ伝える。
この形が理解できると、
catch (SQLException e) {
を単なる「データベースエラーを全部捕まえる場所」としてではなく、
「データベースから返された異常の意味を確認し、必要なものだけ適切に扱う場所」
として考えられるようになります。
私自身、外部キー制約違反に初めて出会ったときは、
「DELETE文は正しいのに、なぜ削除できないのだろう？」
と考え、Javaコードの中ばかりに原因を探していました。
しかし実際には、
「削除できないのではなく、削除してはいけないデータをデータベースが守っていた」
ということでした。
そしてもう一つ、この経験から学んだことがあります。
データベースアプリケーションの問題を調べるときは、
Java
↓
JDBC
↓
SQL
↓
DBのスキーマ・制約・データ
まで視野に入れて原因を探す必要があります。
Javaを書いている最中ほど、頭の中がJavaだけになりがちです。
そんなときこそ、
「データベース側には、どのようなルールを設定していたか？」
と思い出すことが、思わぬ近道になるかもしれません。
外部キー制約を単なるエラーの原因としてではなく、データの整合性を守る仕組みとして理解し、Java側だけでなくデータベース側まで含めて見るようになると、データベースアプリケーションの動きがずっと分かりやすくなると思います。