回答

SEQUENCE（例えば、PostgreSQLやOracleでは）— 現在の値を保持し、挿入用のユニークな番号を取得するために使用されるデータベースの特別なオブジェクトです（通常はキー）。 IDENTITY — 挿入時に次の順序の値を自動的に割り当てるフィールドのプロパティです（SQL Server、MySQL、PostgreSQLのテーブルのオートインクリメント）。

主な違い:

SEQUENCE — テーブル外のオブジェクトであり、異なる場所からアクセス可能で、挿入なしで次の値を取得できます（nextval / currval）。異なるテーブルで使用され、1つのテーブルに対して複数のSEQUENCEが存在する可能性があります。
IDENTITY — 列の定義の一部であり、行挿入時にのみ自動的に増加します。

例:

-- PostgreSQL SEQUENCE
CREATE SEQUENCE order_seq;
INSERT INTO orders(id, name) VALUES (nextval('order_seq'), 'First Order');

-- PostgreSQL IDENTITY
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT
);
INSERT INTO users(username) VALUES ('ivan');  -- idが自動的に割り当てられます

問題と注意点:

手動でidを挿入すると、"穴"や重複が発生する可能性があります。
行を削除すると、値は再利用されず、キーの範囲にギャップが生じる可能性があります。
一部のDBMS（例えばMySQL）では、トランザクションがロールバックされると、autoincrementが先に進むことがあり、値が変更されないことがあります。
SEQUENCEは複数のテーブルで使用できますが、一意性の維持が複雑になります。

トリック質問

もし同時に2つのクエリが同じSEQUENCEからnextval()を要求したらどうなりますか？重複した値を取得する可能性はありますか？

回答: いいえ、SEQUENCEは一意性を保証します。各リクエストは、同時呼び出しでもユニークな値を取得します。ただし、並行性のために挿入の順序に対応しない場合があります。

例:

-- 第1スレッドがnextval=100を取得し、第2スレッドがしばらくしてからnextval=101を取得します。
-- たとえその後に第1スレッドがロールバックしても、値100は既に使用されています。

物語 #1

MySQLからPostgreSQLへの移行後、SEQUENCEとIDENTITYの違いを忘れ、手動でidを挿入しようとした結果、自動生成時に"duplicate key"エラーが発生しました。SEQUENCEにキー管理を完全に移行して修正しました。

物語 #2

autoincrement付きのテーブルから行を部分的に削除した際、idの範囲を"密に"埋める必要がありました。無制御な新しい行の再生成により、ビジネスロジックのキーが不整合になり、管理のために別のSEQUENCEに移行する必要がありました。

物語 #3

大規模なOLTPシステムでSEQUENCEの管理が不十分だったため、注文番号が2,147,483,647を超え、統合APIでの障害を引き起こしました。APIはbigintに対する値を受け入れられなかったため、リクエストは制限付きのSEQUENCEで再実装されました。

SQLにおけるユニークキー自動生成時のシーケンス（SEQUENCE/IDENTITY）の生成と処理はどのように機能し、どのように異なりますか？どのような潜在的な問題が発生する可能性がありますか？

回答

トリック質問