IT未経験・プログラミング初心者が初めてWebアプリケーションを作った際の出来事です。トラブルがいつの間にか解決、「なるほど……？」と納得しかけましたが、調べるとまったく異なる原因でした。
同様の疑問を持った方の他、初心者の思考回路を知りたい方のためになれば嬉しいです。
概要
使用言語：Java
使用ソフト：Eclipse
作成物：会員制のカラオケ予約サイト
エラー発生元：会員、店舗等、新規登録する機能全般。
発生状況：新規登録しようとするとエラーが発生。→「主キーの重複」
解決方法(?)：試行回数が予め入れたサンプルデータの数を越して以降、問題なく登録されるようになった。
詳細
例：会員新規登録
今回各テーブルの主キーをserial型で設定しています。INSERT文では主キー(会員番号=number)を空欄にしそれ以外を入力することで、会員番号(number)は自動採番させようといった魂胆でした。
-- 会員テーブルの作成
CREATE TABLE vips(
number SERIAL PRIMARY KEY,
mail VARCHAR(100) NOT NULL,
password VARCHAR(20) NOT NULL,
name VARCHAR(50) NOT NULL,
address VARCHAR(10) NOT NULL,
birthday VARCHAR(10) NOT NULL,
tel VARCHAR(15) NOT NULL
);
-- 会員テーブルのサンプルデータ
INSERT INTO vips VALUES(1,'abe@dd.co.jp', 'A1000000', '阿部太郎', '東京都','19841001', '09011111111');
INSERT INTO vips VALUES(2,'iga@dd.co.jp', 'B1100000', '伊賀次郎', '千葉県','19541002', '09022222222');
INSERT INTO vips VALUES(3,'uda@dd.co.jp', 'C1200000', '宇田三郎', '滋賀県', '19391003', '09033333333');
public void addStore(String name, String address, String tel, int size) throws SQLException {
String sql = "INSERT INTO stores(name, address, tel, size) VALUES(?, ?, ?, ?)";
Connection connection = DriverManager.getConnection(url, user, pass);
PreparedStatement st = connection.prepareStatement(sql);
st.setString(1, name);
st.setString(2, address);
st.setString(3, tel);
st.setInt(4, size);
st.executeUpdate();
}
(各情報はjsp内でフォームから取得し、Servletを通してDAOファイルに届ける基本的な流れです。)
顛末
いざWebアプリから新規会員登録をしようとしたところ、「主キーが重複している」とエラーメッセージが表示されてしまいました。
Eclipse内でエラー表記はなく、各ファイルを確認してもミスが見当たりません。必要な情報はすべて入力しています。serialなら勝手に振ってくれるんじゃなかったのか？とハテナです。
しかし、何度か挑戦していると…… 突然上手くいきました。
そこで私たちの認識は以下のように変わります。
- 最初の認識
- データベース内で自動採番される。データベース内に既に「1」「2」「3」のデータが存在する場合は、どこからSQL文を実行しても「4」の数値が入る。
- その時抱いたぼんやりとした認識
- 自動採番はデータベース内ではなく、SQL文を実行したファイルで行われる。ファイル内で自動採番された数値がデータベースに登録されるため、PosgreSQLで直接入れた数値と別ファイルで得た数値とで重複する。（？）
原因は 「サンプルデータを直接PostgreSQLに入力していたこと」 だったのか！
と、納得しかけましたが、この記事を書く段階でその認識は誤っていたことに気が付きました。
顛末（改）
結論、原因は 「サンプルデータを直接PostgreSQLに入力した際、Serialに明示的な値を入れたこと」 でした。
そもそもシーケンス(Serial)に対する認識が間違っていたのです。
Serialを使えばデータベース内で自動的に採番されるというぼんやりとした認識のみで、シーケンスオブジェクトの役割をわかっていませんでした。
シーケンスオブジェクトを自分で生成しなくてよいSerialを使用したことも、その勘違いを助長させたのだと思います……
例えば直接1~4まで明示的な値を入れたとしても、シーケンスオブジェクトは自身で絶対的にカウントしているため、 「今4まであるから次は5だな」といった再定義は行われないということです。
おわりに
今回は研修の中で生まれた疑問について調べていきました。
言われてみれば確かになといった感想です。
自分なりに調べて納得しただけなので、間違っているかもしれません。
何かおかしな点や補足があればぜひコメントをお願いします。
エンジニア1年目、これからも頑張っていきます！
参考にさせていただいたサイト
・PostgreSQLでINSERT時に自動採番の主キーが重複してエラーが出る場合の対処法 -- ぺけみさお (xmisao.com)
・MySQL/PostgreSQLでのシリアル値まとめ - adiary開発日誌
(作成日：2023/7/31)