TABLE_CONSTRAINTS 视图¶

此 Information Schema 视图为指定（或当前）数据库中定义的每个表约束显示一行。该视图返回以下约束类型的相关信息：

PRIMARY KEY
FOREIGN KEY
UNIQUE

有关约束的一般信息，请参阅约束。

另请参阅：: REFERENTIAL_CONSTRAINTS 视图

列¶

列名称	数据类型	描述
CONSTRAINT_CATALOG	TEXT	约束所属的数据库
CONSTRAINT_SCHEMA	TEXT	约束所属的架构
CONSTRAINT_NAME	TEXT	约束的名称
TABLE_CATALOG	TEXT	当前表的数据库名称
TABLE_SCHEMA	TEXT	当前表的架构名称
TABLE_NAME	TEXT	当前表的名称
CONSTRAINT_TYPE	TEXT	约束类型
IS_DEFERRABLE	TEXT	对约束的评估是否可以推迟
INITIALLY_DEFERRED	TEXT	对约束的评估是否可以推迟，是否进行了初始推迟
ENFORCED	TEXT	约束是否强制执行
COMMENT	TEXT	对此约束的注释
CREATED	TIMESTAMP_LTZ	约束的创建时间
LAST_ALTERED	TIMESTAMP_LTZ	对象上次经 DML、DDL 或后台元数据操作修改的日期和时间。请参阅使用说明。
RELY	TEXT	在查询重写期间是否考虑 NOVALIDATE 模式下的约束条件。有关详细信息，请参阅约束属性。

使用说明¶

该视图仅显示会话的当前角色已被授予访问权限的对象。
对对象执行以下操作时，将更新 LAST_ALTERED 列：
- DDL 操作。
- DML 操作（仅适用于表）。即使 DML 语句不影响任何行，也会更新此列。
- 由 Snowflake 对元数据执行的后台维护操作。

示例¶

创建带有多列 PRIMARY KEY 约束和约束注释的混合表。查询视图以获取有关约束的信息。

CREATE OR REPLACE HYBRID TABLE HT2PK
  (col1 NUMBER(38,0) NOT NULL,
  col2 NUMBER(38,0) NOT NULL,
  col3 VARCHAR(16777216),
  CONSTRAINT PKEY_2 PRIMARY KEY (col1, col2) COMMENT 'Primary key on two columns');

SELECT constraint_name, table_name, constraint_type, enforced, comment
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE COMMENT IS NOT NULL;

Copy

+-----------------+------------+-----------------+----------+----------------------------+
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED | COMMENT                    |
|-----------------+------------+-----------------+----------+----------------------------|
| PKEY_2          | HT2PK      | PRIMARY KEY     | YES      | Primary key on two columns |
+-----------------+------------+-----------------+----------+----------------------------+

返回名称以 HT 开头的所有表的约束列表：

SELECT constraint_name, table_name, constraint_type, enforced, comment
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE table_name LIKE 'HT%'
  ORDER BY table_name;

Copy

+-----------------------------------------------------+------------------------+-----------------+----------+----------------------------+
| CONSTRAINT_NAME                                     | TABLE_NAME             | CONSTRAINT_TYPE | ENFORCED | COMMENT                    |
|-----------------------------------------------------+------------------------+-----------------+----------+----------------------------|
| SYS_CONSTRAINT_da2e8533-5501-4862-ae42-0a7798d578eb | HT01                   | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_5b3c6d13-f607-4ef6-a147-0026bae98c71 | HT1                    | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_d5887706-0e3b-4d5b-8787-e3327cdf4851 | HT100                  | PRIMARY KEY     | YES      | NULL                       |
| PK1                                                 | HT1PK                  | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_f1d1e153-cc32-477c-9a24-5c049e40ca0a | HT239                  | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_fe27c4f3-23f6-4091-92c4-5acd53cc5029 | HT239                  | UNIQUE          | YES      | NULL                       |
| PKEY_2                                              | HT2PK                  | PRIMARY KEY     | YES      | Primary key on two columns |
| SYS_CONSTRAINT_0bd41d0f-11f7-4366-82a3-f03f31fcce7e | HT616                  | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_6124310b-5f50-4009-a5c0-dc1b5a89b0bc | HT616                  | UNIQUE          | YES      | NULL                       |
| SYS_CONSTRAINT_bf3d76ba-de1e-4227-954f-9f53de777ed4 | HT619                  | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_c97bfe9b-6098-4b8a-b796-e341071db72a | HT619                  | FOREIGN KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_6e02d776-1759-449e-aece-467aaaefcfc8 | HTFK                   | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_51118aaf-1ee6-4548-bc9a-f87e65d92528 | HTFK                   | FOREIGN KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_fe27c4f3-23f6-4091-92c4-5acd53cc5029 | HTLIKE                 | UNIQUE          | YES      | NULL                       |
| SYS_CONSTRAINT_f1d1e153-cc32-477c-9a24-5c049e40ca0a | HTLIKE                 | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_aad16788-491a-4e68-b0e3-30d48a33a1c1 | HTPK                   | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_0bdff17e-e90a-4929-99c5-98e3597e3069 | HTT1                   | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_39e9110f-7a72-454e-bfe2-0a26eca97e7c | HT_PRECIP              | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_6acd8274-04e7-4b22-b9ae-29185b979219 | HT_SENSOR_DATA_DEVICE1 | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_39e9110f-7a72-454e-bfe2-0a26eca97e7c | HT_WEATHER             | PRIMARY KEY     | YES      | NULL                       |
| SYS_CONSTRAINT_843d828a-900d-409e-a57d-8f27b602eccf | HT_WEATHER             | PRIMARY KEY     | YES      | NULL                       |
+-----------------------------------------------------+------------------------+-----------------+----------+----------------------------+