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 }

Copy

其中：

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

Copy

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 } ]

Copy

外联约束语法¶

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

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

Copy

其中：

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>' ]

Copy

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>' ]

Copy

约束属性¶

为了与其他数据库兼容以及与混合表配合使用，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 }

Copy

{ ENFORCED | NOT ENFORCED }

指定是否在事务中强制执行约束。对于标准表，NOT NULL 是 Snowflake 强制执行的唯一约束类型，与此属性无关。

对于混合表，不能在 PRIMARY KEY、FOREIGN KEY 和 UNIQUE 约束条件上设置 NOT ENFORCED 属性。设置该属性会导致“invalid constraint property”错误。

另请参阅参照完整性约束。

默认：NOT ENFORCED

{ DEFERRABLE | NOT DEFERRABLE }

指定在后续事务中是否可将约束检查推迟到事务结束。

默认：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 属性可以提高查询性能（例如，通过消除不必要的联接）。

对于主键约束和外键约束，请在主键约束和外键约束上设置此属性。例如：

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;

Copy

默认：NORELY

属性（仅适用于外键约束）¶

以下约束属性仅适用于外键（属性的顺序可以互换）：

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

Copy

MATCH { FULL | PARTIAL | SIMPLE }

指定一列或多列中的 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');

Copy

列定义中的 COMMENT 子句可用于对列本身或其约束进行注释：

CREATE OR REPLACE TABLE uni (c1 INT UNIQUE COMMENT 'Unique column', c2 int);

Copy

请注意以下限制：

使用 COMMENT 命令不能设置约束注释。
DESCRIBE TABLE 命令显示列上定义的注释，但不显示约束上定义的注释。要查看有关约束的注释，请从 TABLE_CONSTRAINTS 视图或 REFERENTIAL_CONSTRAINTS 视图中选择。
列和约束定义中的 COMMENT 子句不支持等号 (=)。请勿指定：
```
COMMENT = 'My comment'
```
Copy
使用前面示例中的语法：
```
COMMENT 'My comment'
```
Copy

使用说明¶

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) ...
```
Copy
在这两种情况下，列的顺序均为 c_1, c_2。如果外键中列的顺序不同（例如 c_2, c_1），则创建外键的尝试将失败。

访问控制要求¶

要创建主键或唯一约束：

更改现有表以添加约束时，必须使用对该表拥有 OWNERSHIP 权限的角色。
创建新表时，必须使用对要创建表的架构拥有 CREATE TABLE 权限的角色。

要创建外键约束：

必须使用对外键表拥有 OWNERSHIP 权限的角色。
必须使用对唯一/主键表拥有 REFERENCES 权限的角色。

可以使用 GRANT <privileges> 和 REVOKE <privileges> 命令授予角色和撤销角色的 REFERENCES 权限：

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

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

Copy

使用标准表的示例¶

有关混合表约束的示例，请参阅 CREATE HYBRID TABLE。

下面的示例显示了如何在创建表时创建简单的 NOT NULL 约束，以及如何在更改表时创建另一个 NOT NULL 约束：

创建表并同时创建约束：

CREATE TABLE table1 (col1 INTEGER NOT NULL);

Copy

更改表以添加具有约束的列：

ALTER TABLE table1 ADD COLUMN col2 VARCHAR NOT NULL;

Copy

下面的示例指定了列的目的是保存唯一值，但明确指出实际上并未强制执行该约束。此示例还演示了如何指定约束的名称（在本例中为“uniq_col3”。）

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

Copy

下面的示例创建了一个具有主键约束的父表和另一个具有外键约束的表，该外键约束指向与第一个表的主键约束相同的列。

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
);

Copy