CREATE | ALTER TABLE ... CONSTRAINT¶

このトピックでは、 CREATE TABLE または CREATE HYBRID TABLE、 ALTER TABLE ステートメントで CONSTRAINT 句を指定して、制約を作成する方法について説明します。

インライン制約は、個々の列定義の一部として指定されます。
アウトライン制約は、独立した句として指定されます。
- テーブルを作成するとき、句はテーブルの列定義の一部です。
- テーブルを変更する場合、句はテーブルの明示的な ADD アクションとして指定されます。

詳細については、制約をご参照ください。

ハイブリッド・テーブルを作成または変更する場合、制約を定義する構文は同じですが、ルールと要件は異なります。

インライン制約の構文¶

CREATE TABLE <name> ( <col1_name> <col1_type>    [ NOT NULL ] { inlineUniquePK | inlineFK }
                     [ , <col2_name> <col2_type> [ NOT NULL ] { inlineUniquePK | inlineFK } ]
                     [ , ... ] )

ALTER TABLE <name> ADD COLUMN <col_name> <col_type> [ NOT NULL ] { inlineUniquePK | inlineFK }

条件：

inlineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY }
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]

inlineFK :=
  [ CONSTRAINT <constraint_name> ]
  [ FOREIGN KEY ]
  REFERENCES <ref_table_name> [ ( <ref_col_name> ) ]
  [ MATCH { FULL | SIMPLE | PARTIAL } ]
  [ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
       [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]

行外制約の構文¶

CREATE TABLE <name> ... ( <col1_name> <col1_type>
                         [ , <col2_name> <col2_type> , ... ]
                         [ , { outoflineUniquePK | outoflineFK } ]
                         [ , { outoflineUniquePK | outoflineFK } ]
                         [ , ... ] )

ALTER TABLE <name> ... ADD { outoflineUniquePK | outoflineFK }

条件：

outoflineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY } ( <col_name> [ , <col_name> , ... ] )
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
  [ COMMENT '<string_literal>' ]

outoflineFK :=
  [ CONSTRAINT <constraint_name> ]
  FOREIGN KEY ( <col_name> [ , <col_name> , ... ] )
  REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
  [ MATCH { FULL | SIMPLE | PARTIAL } ]
  [ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
       [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
  [ COMMENT '<string_literal>' ]

制約のプロパティ¶

他のデータベースとの互換性や、ハイブリッドテーブルでの使用のために、Snowflakeは制約プロパティを提供します。制約に指定できるプロパティは、タイプによって異なります。

一部のプロパティはすべてのキー（一意、プライマリ、および外部）に適用されます。
他のプロパティは外部キーにのみ適用されます。

重要

Snowflake標準テーブルでは、これらのプロパティは、他のデータベースからの移行を容易にするために提供されています。Snowflakeによって強制保守されていません。これは、これらのプロパティのデフォルトを変更できることを意味しますが、デフォルトを変更すると、Snowflakeが制約を作成しなくなります。

例外は RELY プロパティです。標準テーブルのデータが UNIQUE、 PRIMARY KEY、 FOREIGN KEY 制約に準拠していることを確認した場合、これらの制約に RELY プロパティを設定できます。不要な結合を削除するための RELY 制約プロパティの設定もご参照ください。

ハイブリッド・テーブルを作成または変更する場合は、ルールと要件が異なります。制約の概要をご参照ください。

サポートされている制約プロパティのほとんどは、 ANSI SQL 標準プロパティですが、以下のプロパティは Snowflake 拡張です。

ENABLE | DISABLE
VALIDATE | NOVALIDATE
RELY | NORELY

また、行外制約定義内でコメントを定義することもできます。制約に対するコメントをご参照ください。

すべての制約のプロパティ¶

次の制約プロパティはすべてのキーに適用されます（プロパティの順序は交換可能です）。

[ NOT ] ENFORCED
[ NOT ] DEFERRABLE
INITIALLY { DEFERRED | IMMEDIATE }
{ ENABLE | DISABLE }
{ VALIDATE | NOVALIDATE }
{ RELY | NORELY }

{ ENFORCED | NOT ENFORCED }

トランザクションで制約を強制するかどうかを指定します。標準テーブルの場合、 NOT NULL は、このプロパティに関係なく、Snowflakeによって強制される 唯一の 制約タイプです。

ハイブリッド・テーブルでは、 PRIMARY KEY 、 FOREIGN KEY 、 UNIQUE 制約に NOT ENFORCED プロパティを設定することはできません。このプロパティを設定すると、「無効な制約プロパティ」エラーが発生します。

参照整合性制約もご参照ください。

デフォルト: NOT ENFORCED

{ DEFERRABLE | NOT DEFERRABLE }

後続のトランザクションで、トランザクションの終了まで制約のチェックを延期できるかどうかを指定します。

デフォルト: NOT DEFERRABLE

INITIALLY { DEFERRED | IMMEDIATE }

DEFERRABLE 制約の場合、次のトランザクションから制約のチェックを延期するかどうかを指定します。

デフォルト: INITIALLY DEFERRED

{ ENABLE | DISABLE }

制約を有効にするか無効にするかを指定します。これらのプロパティは、Oracleとの互換性のために提供されています。

デフォルト: DISABLE

{ VALIDATE | NOVALIDATE }

制約が作成されたときに、テーブル上の既存のデータを検証するかどうかを指定します。{ ENFORCED | NOT ENFORCED } 、 { ENABLE | DISABLE } のいずれかが指定された場合のみ適用されます。

デフォルト: NOVALIDATE

{ RELY | NORELY }

クエリの書き換え中に NOVALIDATE モードの制約を考慮するかどうかを指定します。

テーブルのデータが制約に適合していることを確認した場合、このプロパティを RELY に変更することで、クエリオプティマイザがそのようなデータ整合性を期待すべきことを示すことができます。標準的なテーブルの場合、RELY 制約を強制するのはあなたの責任です。そうしないと、意図しない動作や予期しない結果が発生する危険性があります。

RELY プロパティが制約に設定され、参照整合性の違反が発生した場合、DML および CTAS ステートメントは誤ったデータを挿入する可能性があります。

RELY プロパティを設定することで、クエリのパフォーマンスが向上する可能性があります（例えば、不要な結合を排除することによって）。

関連する PRIMARYKEY および FOREIGNKEY 制約の場合は、このプロパティを両方の制約で設定します。例:

ALTER TABLE table_with_primary_key ALTER CONSTRAINT a_primary_key_constraint RELY;
ALTER TABLE table_with_foreign_key ALTER CONSTRAINT a_foreign_key_constraint RELY;

デフォルト: NORELY

プロパティ（外部キー制約のみ）¶

次の制約プロパティは、外部キーにのみ適用されます（プロパティの順序は交換可能）。

MATCH { FULL | SIMPLE | PARTIAL }
ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
   [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]

MATCH { FULL | PARTIAL | SIMPLE }

1つ以上の列の NULL 値に関して外部キー制約が満たされるかどうかを指定します。

デフォルト: MATCH FULL

UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }

外部キーの主/一意キーが更新されたときに実行されるアクションを指定します。

デフォルト: UPDATE NO ACTION

DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }

外部キーの主/一意キーが削除されたときに実行されるアクションを指定します。

デフォルト: DELETE NO ACTION

ENABLE と VALIDATE プロパティのデフォルト以外の値¶

他のデータベースと構文の互換性を保つために、Snowflakeは制約プロパティにデフォルト以外の値を指定することをサポートしています。

ただし、新しい制約を作成するときに ENABLE または VALIDATE （こうしたプロパティのデフォルト以外の値）を指定すると、 制約は作成されません。これは、 RELY には適用されません。RELY を指定すると、新しい制約が作成されます。

Snowflakeには、制約の作成中にデフォルト以外の値を指定するとエラーが発生するかどうかを指定するセッションパラメーターである UNSUPPORTED_DDL_ACTION が用意されています。

制約に対するコメント¶

他のデータベースオブジェクトや構築と同様に、Snowflakeは制約に対するコメントの提供をサポートしています。

行外制約は、制約定義内の COMMENT 句をサポートします。
```
CREATE OR REPLACE TABLE uni (c1 INT, c2 int, CONSTRAINT uni1 UNIQUE(C1) COMMENT 'Unique column');
```
カラム定義内の COMMENT 句は、カラム自体やその制約についてコメントするために使用できます。
```
CREATE OR REPLACE TABLE uni (c1 INT UNIQUE COMMENT 'Unique column', c2 int);
```

以下の制限にご注意ください。

COMMENT コマンドを使用して制約にコメントを設定することはできません。
DESCRIBE TABLE コマンドは列に定義されたコメントを表示しますが、制約に定義されたコメントは表示しません。制約に関するコメントを見るには、 TABLE_CONSTRAINTS ビューまたは REFERENTIAL_CONSTRAINTS ビューから選択します。
列および制約定義内の COMMENT 句は、等号（=）をサポートしていません。指定しません。
```
COMMENT = 'My comment'
```
前の例で示した構文を使います。
```
COMMENT 'My comment'
```

使用上の注意¶

NOT NULL 列が NULL 値を許可しないことを指定します。
- 標準的なSnowflakeテーブルでは、これが唯一の制約です。参照整合性制約をご参照ください。
- 列定義内のインライン制約としてのみ指定できます。
- デフォルトでは、列に NULL 値を許可します。
複数列の制約（例: 複合一意キーまたは主キー）は、アウトラインでのみ定義できます。
インラインまたはアウトラインの外部キーを定義する場合、外部キー列と参照先テーブルの主キー列の署名（つまり、名前とデータ型）が完全に一致すれば、参照先テーブルの列名を指定する必要はありません。

外部キーを作成する場合、 REFERENCES 句の列は、主キーの列と同じ順序でリストする必要があります。例:
```
CREATE TABLE parent ... CONSTRAINT primary_key_1 PRIMARY KEY (c_1, c_2) ...
CREATE TABLE child  ... CONSTRAINT foreign_key_1 FOREIGN KEY (...) REFERENCES parent (c_1, c_2) ...
```
どちらの場合も、列の順序は c_1, c_2 です。外部キーの列の順序が異なっていた場合（例: c_2, c_1）、外部キーを作成する試みは失敗していました。

アクセス制御の要件¶

主キー制約または一意制約を作成する場合：

制約を追加するために既存のテーブルを変更する場合、 OWNERSHIP 権限を持つロールを使用する必要があります。
新しいテーブルを作成する際には、テーブルが作成されるスキーマで CREATE TABLE 権限を持つロールを使用する必要があります。

外部キー制約を作成する場合：

テーブルで OWNERSHIP 権限を持つロールを使用することも必要です。
一意/キーテーブルで REFERENCES 権限を持つロールを使用することも必要です。

GRANT <権限> ... TO ROLE および REVOKE <権限> ... FROM ROLE コマンドを使用して、ロールへのREFERENCES権限の付与および取り消しができます。

GRANT REFERENCES ON TABLE <pk_table_name> TO ROLE <role_name>

REVOKE REFERENCES ON TABLE <pk_table_name> FROM ROLE <role_name>

標準テーブルを使った例¶

ハイブリッド・テーブルを使用した制約の例については、 CREATE HYBRID TABLE をご参照ください。

以下の例は、テーブルの作成中に単純な NOT NULL 制約を作成し、テーブルの変更中に別の NOT NULL 制約を作成する方法を示しています。

テーブルを作成し、同時に制約を作成します。

CREATE TABLE table1 (col1 INTEGER NOT NULL);

テーブルを変更して、制約付きの列を追加します。

ALTER TABLE table1 ADD COLUMN col2 VARCHAR NOT NULL;

次の例では、列の意図が一意の値を保持することを指定していますが、制約が実際に適用されていないことを明確にします。この例は、制約の名前（この場合は「uniq_col3」）の指定方法も示しています。

ALTER TABLE table1 
  ADD COLUMN col3 VARCHAR NOT NULL CONSTRAINT uniq_col3 UNIQUE NOT ENFORCED;

以下は、主キー制約を持つ親テーブルと、最初のテーブルの主キー制約と同じ列を指す外部キー制約を持つ別のテーブルを作成します。

CREATE TABLE table2 (
  col1 INTEGER NOT NULL,
  col2 INTEGER NOT NULL,
  CONSTRAINT pkey_1 PRIMARY KEY (col1, col2) NOT ENFORCED
);
CREATE TABLE table3 (
  col_a INTEGER NOT NULL,
  col_b INTEGER NOT NULL,
  CONSTRAINT fkey_1 FOREIGN KEY (col_a, col_b) REFERENCES table2 (col1, col2) NOT ENFORCED
);