SQL Identity

概要: このチュートリアルでは、GENERATED AS IDENTITY を使用して、テーブルの SQL Identity 列を作成する方法を学びます。

SQL Identity 列の紹介

SQL Identity 列は、テーブルに新しい行を追加すると値が自動的に生成される列です。Identity 列を定義するには、次のように GENERATED AS IDENTITY プロパティを使用します。

column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]
Code language: SQL (Structured Query Language) (sql)

この構文では

• data_type は、任意の整数データ型にすることができます。
• GENERATED ALWAYS は、Identity 列にシーケンシャルな整数を生成します。挿入（または更新）しようとするとGENERATED ALWAYS AS IDENTITY列に値を挿入すると、データベースシステムはエラーを発生させます。
• GENERATED BY DEFAULT は、Identity 列にシーケンシャルな整数を生成します。ただし、挿入または更新の値を指定すると、データベースシステムは自動生成値の代わりにその値を挿入に使用します。

SQL Identity 列の例

A) GENERATED ALWAYS の例

まず、テーブルを作成します。そのテーブルの名前をranksとし、rank_id列をIdentity列とします。

CREATE TABLE ranks (
    rank_id INT GENERATED ALWAYS AS IDENTITY,
    rank_name CHAR
);
Code language: SQL (Structured Query Language) (sql)

次に、新しい行をranksテーブルに挿入します。

INSERT INTO ranks (rank_name)
VALUES
    ('A');
Code language: SQL (Structured Query Language) (sql)

rank_id列にGENERATED AS IDENTITYプロパティがあるため、データベースシステムは以下のようにシーケンシャルな整数を生成します。

SELECT
    *
FROM
    ranks;
Code language: SQL (Structured Query Language) (sql)

次に、rank_id列とrank_name列の両方に値を指定して新しい行を挿入します。

INSERT INTO ranks (rank_id, rank_name)
VALUES
    (2, 'B');
Code language: SQL (Structured Query Language) (sql)

データベースシステムは次のエラーを発行しました。

[Err] ERROR:  cannot insert into column "rank_id"
DETAIL:  Column "rank_id" is an identity column defined as GENERATED ALWAYS.
Code language: SQL (Structured Query Language) (sql)

このエラーを修正するには、GENERATED BY DEFAULT AS IDENTITYを使用します。

B) GENERATED BY DEFAULT AS IDENTITY の例

まず、削除してranksテーブルを再作成します。今回はGENERATED BY DEFAULT AS IDENTITYプロパティを使用します。

DROP TABLE ranks;

CREATE TABLE ranks (
    rank_id INT GENERATED BY DEFAULT AS IDENTITY,
    rank_name CHAR NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

次に、ranksテーブルに行を挿入します。

INSERT INTO ranks (rank_name)
VALUES
    ('A');
Code language: SQL (Structured Query Language) (sql)

期待通りに動作します。

次に、rank_id列の値を持つ別の行を挿入します。

INSERT INTO ranks (rank_id, rank_name)
VALUES
    (2, 'B');
Code language: SQL (Structured Query Language) (sql)

GENERATED ALWAYS AS IDENTITYを使用する前の例とは異なり、このステートメントも機能することに注意してください。

C) シーケンスオプションの例

次の構文を使用して、Identity 列の開始値と増分値を指定できます。

START WITH starting_value 
INCREMENT BY increment_value;
Code language: SQL (Structured Query Language) (sql)

次の例を参照してください。

DROP TABLE ranks;

CREATE TABLE ranks (
    rank_id INT GENERATED BY DEFAULT AS IDENTITY 
    (START WITH 10 INCREMENT BY 10),
    rank_name CHAR NOT NULL
); 
Code language: SQL (Structured Query Language) (sql)

この例では、rank_id列の自動生成値は10から始まり、増分値も10です。

まず、ranksテーブルに新しい行を挿入します。

INSERT INTO ranks (rank_name)
VALUES
    ('A');
Code language: SQL (Structured Query Language) (sql)

rank_id列の開始値は以下のように10です。

SELECT
    *
FROM
    ranks;
Code language: SQL (Structured Query Language) (sql)

次に、ranksテーブルに別の行を挿入します。

INSERT INTO ranks (rank_name)
VALUES
    ('B');
Code language: SQL (Structured Query Language) (sql)

増分値オプションのため、2行目のrank_idの値は20になります。

SELECT
    *
FROM
    ranks;
Code language: SQL (Structured Query Language) (sql)

このチュートリアルでは、GENERATED AS IDENTITYを使用してSQL Identity列を定義する方法を学びました。