CONSTRAINT

​ CONSTRAINT子句是指限制和索引相似，虽然限制也能被用于建立和另一个表的关联，用ALTER TABLE和CREATE TABLE语句中的CONSTRAINT子句来建立或删除条件。CONSTRAINT子句可分为两种类型：第一种是在单一字段上创建条件；第二种是在一个以上的字段上创建条件。

ADD CONSTRAINT

ADD CONTRAINT语句可以为列添加以下约束：

  • CHECK

  • FOREIGN KEY

  • UNIQUE

主键的NOT NULL约束只能通过CREATE TABLE创建。DEFAULT约束通过ALTER COLUMN管理。

语法格式

新增约束的语法格式如下：

CONSTRAINT - 图1

因为新增约束语句是ALTER TABLE的一个子句，因此有关修改表的一些语句不再进行说明。详情请见ALTER TABLE。

参数说明

  • table_name

需添加约束的列所在的表。

  • constraint_name

约束名，必须在表内唯一，并且符合命名规范。

  • constraint_elem

要添加的约束：CHECK、FOREIGN KEY、UNIQUE

通过 ALTER COLUMN添加或修改一个默认约束。

修改表的主键无法通过ALTER TABLE完成，只能在表格创建时指定。

示例

示例1：添加约束UNIQUE

添加UNIQUE约束要求该列的每一个值都是唯一的，NULL除外。

ALTER TABLE office_dogs ADD CONSTRAINT id_customer_unique UNIQUE (id, full_name);

示例2：添加约束CHECK

添加CHECK约束，要求所有列的值通过布尔表达式计算后为TRUE。

ALTER TABLE office_dogs ADD CONSTRAINT alive_check CHECK (alive=’true’);

示例3：通过CASCADE添加FOREIGN KEY约束

在添加FOREIGN KEY约束前，该列必须已添加索引。如果该列未索引，使用CREATE INDEX来创建索引，然后再通过ADD CONSTRAINT添加FOREIGN KEY约束。要添加外键约束，请使用以下步骤。

给定两个表，office_dogs和address:

SHOW CREATE TABLE office_dogs;

table_name | create_statement

+——————-+————————————————————————————————————————————————————-+

office_dogs | CREATE TABLE office_dogs ( alive STRING NULL, rides_id UUID NULL, rides_length INTERVAL NULL, rides_city STRING NULL, address_id UUID NULL, CONSTRAINT “primary” PRIMARY KEY ( ID ASC ), UNIQUE INDEX id_customer_unique ( ID ASC, full_name ASC ), INDEX office_dogs_rides_id_idx ( rides_id ASC ), INDEX office_dogs_rides_length_rides_city_idx ( rides_length ASC, rides_city ASC ), FAMILY “primary” ( ID, last_name, full_name, alive, rides_id, rides_length, rides_city, address_id ), CONSTRAINT alive_check CHECK ( alive = ‘true’ ) ENABLE );

SHOW CREATE TABLE address;

table_name | create_statement

+——————+————————————————————————+

address | CREATE TABLE address ( ID UUID NOT NULL, city STRING NOT NULL, CONSTRAINT “primary” PRIMARY KEY ( ID ASC ), FAMILY “primary” ( ID, city ) )；

要确保office_dogs.address_id列中的每个值与address.id列中的唯一值匹配，需要将外键约束添加到office_dogs.address_id。

所以你首先需要在office_dogs.address_id上创建一个索引：

CREATE INDEX ON office_dogs (address_id);

然后再添加FOREIGN KEY约束。

你可以指定一个FOREIGN KEY action来指定当FOREIGN KEY被更新或删除的时候执行什么动作。在下述例子中，我们可以使用ON DELETE CASCADE来添加（当相关行被删除，所有依赖的对象也会被删除）CASCADE不会列出它删除或者更新的对象，所以需要谨慎使用

ALTER TABLE office_dogs ADD CONSTRAINT address_fk FOREIGN KEY (address_id) REFERENCES address (id) ON DELETE CASCADE;

SHOW CONSTRAINTS FROM office_dogs ;

table_name | constraint_name | constraint_type | details | validated

+——————-+——————————+————————-+——————————————————————————————————+—————-+

office_dogs | address_fk | FOREIGN KEY | FOREIGN KEY (address_id) REFERENCES address (id) ON DELETE CASCADE | false office_dogs | alive_check | CHECK | CHECK (alive = ‘true’) ENABLE | true

office_dogs | id_customer_unique | UNIQUE | UNIQUE (id ASC, full_name ASC) | true

office_dogs | primary | PRIMARY KEY | PRIMARY KEY (id ASC) | true

(4 rows) |

SHOW CONSTRAINTS

​ SHOW CONSTRAINTS 语句列出了表上所有已命名的约束以及所有未命名的CHECK约束。任何用户拥有目标表的任意权限即可查看此表上的约束。

语法格式

显示索引的语法格式如下：

CONSTRAINT - 图2

SHOW CONSTRAINT是 SHOW CONSTRAINTS的一个别名。用户需具有目标表的任一权限。

参数说明

  • table_name

需显示约束的表名。

示例

示例1：显示表上的约束

CREATE TABLE orders ( ID INT PRIMARY KEY, DATE TIMESTAMP NOT NULL, priority INT DEFAULT 1, customer_id INT UNIQUE, status STRING DEFAULT ‘open’, CHECK ( priority BETWEEN 1 AND 5 ), CHECK ( status IN ( ‘open’, ‘in progress’, ‘done’, ‘cancelled’ )), FAMILY ( ID, DATE, priority, customer_id, status ) );

SHOW CONSTRAINTS FROM orders;

table_name | constraint_name | constraint_type | details | validated

+——————+————————————+————————-+—————————————————————————————————————————————————————-+—————- orders | check_priority | CHECK | CHECK (priority BETWEEN 1 AND 5) ENABLE | true

orders | check_status | CHECK | CHECK (status IN (‘open’:::STRING, ‘in progress’:::STRING, ‘done’:::STRING, ‘cancelled’:::STRING)) ENABLE | true

orders | orders_customer_id_key | UNIQUE | UNIQUE (customer_id ASC) | true

orders | primary | PRIMARY KEY | PRIMARY KEY (id ASC) | true

(4 rows) |

RENAME CONSTRAINT

​ RENAME CONSTRAINT 语句用于更改列上约束的名称。

语法格式

​ 见ALTER TABLE处的语法格式中有关CONSTRAINT的部分，对于CONSTRAINT的修改目前仅支持名字的修改。

参数说明

  • column_name

前面一个column_name指的是当前的约束名称，后者指的是将要修改的约束名称。

示例

示例1：修改约束名称

SHOW CONSTRAINTS FROM orders;

table_name | constraint_name | constraint_type | details | validated

+——————+————————————+————————-+—————————————————————————————————————————————————————-+————-orders | check_priority | CHECK | CHECK (priority BETWEEN 1 AND 5) ENABLE | true

orders | check_status | CHECK | CHECK (status IN (‘open’:::STRING, ‘in progress’:::STRING, ‘done’:::STRING, ‘cancelled’:::STRING)) ENABLE | true

orders | orders_customer_id_key | UNIQUE | UNIQUE (customer_id ASC) | true

orders | primary | PRIMARY KEY | PRIMARY KEY (id ASC) | true

(4 rows)

ALTER TABLE orders RENAME CONSTRAINT orders_customer_id_key TO orders_customer_id_key_rename;

SHOW CONSTRAINTS FROM orders;

table_name | constraint_name | constraint_type | details | validated

+——————+———————————————-+————————-+—————————————————————————————————————————————————————-+—- orders | check_priority | CHECK | CHECK (priority BETWEEN 1 AND 5) ENABLE | true

orders | check_status | CHECK | CHECK (status IN (‘open’:::STRING, ‘in progress’:::STRING, ‘done’:::STRING, ‘cancelled’:::STRING)) ENABLE | true

orders | orders_customer_id_key_rename | UNIQUE | UNIQUE (customer_id ASC) | true

orders | primary | PRIMARY KEY | PRIMARY KEY (id ASC) | true (4 rows)

DROP CONSTRAINT

​ DROP CONSTRAINT语句是ALTER TABLE的一部分，会删除列CHECK和FOREIGN KEY约束。

语法格式

删除约束的语法格式如下：

CONSTRAINT - 图3

因为删除约束语句是修改表语句的一个子语句，因此对于修改表的一些语法结构将不再重复说明，详情请见ALTER TABLE部分。

如果添加了IF EXISTS，当要删除的约束存在时，删除约束；如果不存在，删除约束不成功，但是不抛出错误。

如果不添加IF EXISTS，当要删除的约束存在时，删除约束；如果不存在，删除约束不成功，抛出错误：约束不存在。

CASCADE级联删除，删除依赖于约束的所有对象，CASCADE不会列出删除的对象，请谨慎使用。

RESTRICT 如果一个存在依赖于约束的对象，那么删除约束会失败。

注意：删除约束的过程取决于其类型：

  • CHECK：使用DROP CONSTRAINT。

  • DEFAULT：使用ALTER COLUMN。

  • FOREIGN KEY：使用DROP CONSTRAINT。

  • NOT NULL：使用ALTER COLUMN。

  • PRIMARY KEY：主键无法删除，但是可以通过其他方式将表的数据移动到新表中（表迁移）。

  • UNIQUE：UNIQUE约束不能直接删除。要删除约束，请删除由约束创建的索引，例如DROP INDEX my_unique_constraint CASCADE（请注意，CASCADE删除唯一约束使用的索引是必需的）。

  • 表迁移：如果要更改不可变约束，可以使用以下过程：

  • 用要应用的约束创建一个新表。

  • 使用INSERT一条SELECT语句将数据从旧表移动到新表。

  • 删除旧表，然后将新表重命名为旧名称。这不能以事务方式完成。

参数说明

  • table_name

要删除约束的表名。

  • name

要删除的约束的名称。

示例

示例1：删除指定约束

SHOW CONSTRAINTS FROM orders; table_name | constraint_name | constraint_type | details | validated

+——————+———————————————-+————————-+—————————————————————————————————————————————————————-+——orders | check_priority | CHECK | CHECK (priority BETWEEN 1 AND 5) ENABLE | true

orders | check_status | CHECK | CHECK (status IN (‘open’:::STRING, ‘in progress’:::STRING, ‘done’:::STRING, ‘cancelled’:::STRING)) ENABLE | true

orders | orders_customer_id_key_rename | UNIQUE | UNIQUE (customer_id ASC) | true orders | primary | PRIMARY KEY | PRIMARY KEY (id ASC) | true

(4 rows)

ALTER TABLE orders DROP CONSTRAINT check_priority;

SHOW CONSTRAINTS FROM orders; table_name | constraint_name | constraint_type | details | validated

+——————+———————————————-+————————-+—————————————————————————————————————————————————————-+——orders | check_status | CHECK | CHECK (status IN (‘open’:::STRING, ‘in progress’:::STRING, ‘done’:::STRING, ‘cancelled’:::STRING)) ENABLE | true

orders | orders_customer_id_key_rename | UNIQUE | UNIQUE (customer_id ASC) | true

orders | primary | PRIMARY KEY | PRIMARY KEY (id ASC) | true

(3 rows) |