PostgreSQL 制約

PostgreSQL 制約は、テーブル内のデータの規則を定めるために使用されます。

制約に違反するデータ操作がある場合、その操作は制約によって中止されます。

制約は、テーブル作成時(CREATE TABLE ステートメントを使用)またはテーブル作成後(ALTER TABLE ステートメントを使用)に定義できます。

制約は、データベース内のデータの正確性と信頼性を保証します。

制約は列レベルの制約またはテーブルレベルの制約です。列レベルの制約は列にのみ適用され、テーブルレベルの制約はテーブル全体に適用されます。

以下は、PostgreSQL で一般的に使用される制約です。

  • NOT NULL: 特定の列に NULL 値を格納できないことを示します。
  • UNIQUE: 特定の列の値がすべて一意であることを保証します。
  • PRIMARY KEY: NOT NULL と UNIQUE の組み合わせです。特定の列(または2つ以上の列の組み合わせ)が一意の識別子を持つことを保証し、テーブル内の特定のレコードをより簡単かつ迅速に見つけるのに役立ちます。
  • FOREIGN KEY: あるテーブルのデータが別のテーブルの値と一致することを保証する参照整合性を確保します。
  • CHECK: 列の値が指定された条件を満たすことを保証します。
  • EXCLUSION: 排他制約。指定された列または式に対して、指定された演算子を使用して任意の2行を比較する場合、少なくとも1つの演算子比較が false または NULL を返すことを保証します。

NOT NULL 制約

デフォルトでは、列は NULL 値を保持できます。列に NULL 値を持たせたくない場合は、その列にこの制約を定義して、その列で NULL 値を許可しないように指定する必要があります。

NULL はデータがないこととは異なり、未知のデータを表します。

例

次の例では、COMPANY1 という新しいテーブルを作成し、5つのフィールドを追加しています。そのうち ID、NAME、AGE の3つは NULL 値を受け付けないように設定されています:

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

UNIQUE 制約

UNIQUE 制約は、列を一意に設定し、同じ列に重複する値が出現するのを防ぎます。

例

次の例では、COMPANY3 という新しいテーブルを作成し、5つのフィールドを追加しています。AGE は UNIQUE に設定されているため、同じ年齢のレコードを2つ追加することはできません。

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

PRIMARY KEY

データベースを設計するとき、PRIMARY KEY は非常に重要です。

PRIMARY KEY は主キーと呼ばれ、データテーブル内の各レコードの一意の識別子です。

UNIQUE に設定できる列は複数ありますが、1つのテーブルで PRIMARY KEY に設定できるのは1列だけです。

主キーを使用してテーブル内の行を参照できます。また、主キーを他のテーブルの外部キーとして設定することで、テーブル間の関係を作成できます。

主キーは、NOT NULL 制約と UNIQUE 制約の組み合わせです。

1つのテーブルには主キーを1つだけ持つことができます。主キーは1つまたは複数のフィールドで構成できます。複数のフィールドが主キーである場合、それらは複合キーと呼ばれます。

テーブルのいずれかのフィールドに主キーが定義されている場合、それらのフィールドに同じ値を持つ2つのレコードが存在することはできません。

例

次に、ID を主キーとする COMAPNY4 テーブルを作成します:

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

FOREIGN KEY 制約

FOREIGN KEY は外部キー制約であり、指定された列(または列のグループ)の値が、別のテーブルのいずれかの行に存在する値と一致する必要があることを指定します。

通常、あるテーブルの FOREIGN KEY は別のテーブルの UNIQUE KEY(一意制約のキー)を参照し、これにより関連する2つのテーブル間の参照整合性が維持されます。

例

次の例では、COMPANY6 というテーブルを作成し、5つのフィールドを追加しています:

CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

次の例では、DEPARTMENT1 というテーブルを作成し、3つのフィールドを追加しています。EMP_ID は外部キーであり、COMPANY6 の ID を参照します:

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
);

CHECK 制約

CHECK 制約は、列内のすべての値が特定の条件を満たすことを保証します。つまり、レコードが入力される際にチェックが行われます。条件値が false の場合、レコードは制約に違反するため、テーブルに入力できません。

例

例えば、次の例では COMPANY5 という新しいテーブルを作成し、5つの列を追加しています。ここでは、SALARY 列に CHECK を追加しているため、給与をゼロにすることはできません。

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

EXCLUSION 制約

EXCLUSION 制約は、指定された演算子を使用して指定された列または式上で任意の2行を比較する場合、少なくとも1つの演算子比較が false または null を返すことを保証します。

例

次の例では、COMPANY7 というテーブルを作成し、5つのフィールドを追加して、EXCLUDE 制約を使用しています。

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,  -- 如果满足 NAME 相同,AGE 不相同则不允许插入,否则允许插入
   AGE WITH <>)   -- 其比较的结果是如果整个表达式返回 true,则不允许插入,否则允许
);

ここでの USING gist は、インデックスの構築と実行に使用されるインデックスタイプの1つです。

データベースごとに CREATE EXTENSION btree_gist コマンドを1回実行する必要があります。これにより btree_gist 拡張機能がインストールされ、純粋なスカラーデータ型に対する EXCLUDE 制約が定義されます。

年齢が同じでなければならないことをすでに強制しているので、テーブルにレコードを挿入してこれを確認してみましょう:

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
-- 此条数据的 NAME 与第一条相同,且 AGE 与第一条也相同,故满足插入条件
INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
-- 此数据与上面数据的 NAME 相同,但 AGE 不相同,故不允许插入

最初の2つは COMPANY7 テーブルに正常に追加されますが、3つ目はエラーになります:

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

制約の削除

制約を削除するには、制約名を知っている必要があります。名前が分かっている場合は削除は簡単ですが、名前が分からない場合は、システムによって生成された名前を見つける必要があります。次のコマンドを使用します:\d テーブル名これらの情報を見つけることができます。

一般的な構文は次のとおりです:

ALTER TABLE table_name DROP CONSTRAINT some_name;
その他の拡張機能