CONSTRAINT
CONSTRAINT子句是指限制和索引相似,虽然限制也能被用于建立和另一个表的关联,用ALTER TABLE和CREATE TABLE语句中的CONSTRAINT子句来建立或删除条件。CONSTRAINT子句可分为两种类型:第一种是在单一字段上创建条件;第二种是在一个以上的字段上创建条件。
ADD CONSTRAINT
ADD CONTRAINT语句可以为列添加以下约束:
CHECK
FOREIGN KEY
UNIQUE
主键的NOT NULL约束只能通过CREATE TABLE创建。DEFAULT约束通过ALTER COLUMN管理。
语法格式
新增约束的语法格式如下:
因为新增约束语句是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约束。任何用户拥有目标表的任意权限即可查看此表上的约束。
语法格式
显示索引的语法格式如下:
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约束。
语法格式
删除约束的语法格式如下:
因为删除约束语句是修改表语句的一个子语句,因此对于修改表的一些语法结构将不再重复说明,详情请见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) |