【经验分享】关于GaussDB约束，你了解多少？

gsql连接数据库

gsql -d db_tpcc -U joe -W Bigdata@123 -p 15400

1.创建表及约束

1.1 建表语法

1.1.1主备式

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name{({ column_name data_type [ CHARACTER SET | CHARSET charset ] [ compress_mode ] [ COLLATEcollation ] [ column_constraint [ ... ] ]| table_constraint| LIKE source_table [ like_option [...] ] }[, ... ])| LIKE source_table }[ table_option [ [ , ] ... ] ][ WITH ( {storage_parameter = value} [, ... ] ) ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ] [ COMPRESS | NOCOMPRESS ][ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF{ NO MODIFICATION } [ ON ( EXPR )]][ TABLESPACE tablespace_name ];

● 其中列约束column_constraint为：

[ CONSTRAINT constraint_name ]{ NOT NULL |NULL |CHECK ( expression ) |DEFAULT default_expr |ON UPDATE update_expr |GENERATED ALWAYS AS ( generation_expr ) [STORED] |GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ] |AUTO_INCREMENT |COMMENT 'string' |UNIQUE [KEY] index_parameters |PRIMARY KEY index_parameters |ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE =encryption_type_value ) |REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ][ ON DELETE action ] [ ON UPDATE action ] }[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

● 其中表约束table_constraint为：

[ CONSTRAINT [ constraint_name ] ]{ CHECK ( expression ) |UNIQUE [ index_name ] [ USING method ] ( { { column_name [ ( length ) ] | ( expression ) } [ ASC |DESC ] } [, ... ] ) index_parameters |PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters|FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ][ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATEaction ] }[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]{ [ COMMENT 'string' ] [ ... ] }

● CONSTRAINT constraint_name

列约束或表约束的名称。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。定义约束有两种方法：– 列约束：作为一个列定义的一部分，仅影响该列。– 表约束：不和某个列绑在一起，可以作用于多个列。

● NOT NULL

字段值不允许为NULL。

● CHECK ( expression )

CHECK约束声明一个布尔表达式，每次插入或者更新的行必须使表达式结果为真或未知才能成功，否则会抛出一个异常并且不会修改数据库。

● DEFAULT default_expr

DEFAULT子句给字段指定缺省值。该数值可以是任何不含变量的表达式（不允许使用子查询和对本表中的其他字段的交叉引用）。缺省表达式的数据类型必须和字段类型匹配。缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。

● UNIQUE [KEY] index_parameters

UNIQUE约束表示表里的一个或多个字段的组合必须在全表范围内唯一。UNIQUE KEY只能在B模式数据库下（即sql_compatibility = 'B'）支持使用，与UNIQUE语义相同。

● UNIQUE index_name ( {{ column_name [ ( length ) ]| ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters

UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。对于唯一约束， NULL被认为是互不相等的。column_name(length)是前缀键，详见： •column_name ( length )。index_name为索引名。

● PRIMARY KEY index_parametersPRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] }[, ... ] ） index_parameters

主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。一个表只能声明一个主键。

● REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ONDELETE action ] [ ON UPDATE action ] (column constraint)FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn[, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATEaction ] (table constraint)

外键约束要求新表中一列或多列构成的组应该只包含、匹配被参考表中被参考字段值。若省略refcolumn，则将使用reftable的主键。被参考列应该是被参考表中的唯一字段或主键。外键约束不能被定义在临时表和永久表之间。参考字段与被参考字段之间存在三种类型匹配，分别是：– MATCH FULL：不允许一个多字段外键的字段为NULL，除非全部外键字段都是NULL。– MATCH SIMPLE（缺省）：允许任意外键字段为NULL。– MATCH PARTIAL：目前暂不支持。另外，当被参考表中的数据发生改变时，某些操作也会在新表对应字段的数据上执行。 ON DELETE子句声明当被参考表中的被参考行被删除时要执行的操作。ON UPDATE子句声明当被参考表中的被参考字段数据更新时要执行的操作。对于ON DELETE子句、 ON UPDATE子句的可能动作：– NO ACTION（缺省）：删除或更新时，创建一个表明违反外键约束的错误。   若约束可推迟，且若仍存在任何引用行，那这个错误将会在检查约束的时候产生。– RESTRICT：删除或更新时，创建一个表明违反外键约束的错误。与NO ACTION相同，只是动作不可推迟。– CASCADE：删除新表中任何引用了被删除行的行，或更新新表中引用行的字段值为被参考字段的新值。– SET NULL：设置引用字段为NULL。– SET DEFAULT：设置引用字段为它们的缺省值。

● DEFERRABLE | NOT DEFERRABLE

这两个关键字设置该约束是否可推迟。一个不可推迟的约束将在每条命令之后马上检查。可推迟约束可以推迟到事务结尾使用SET CONSTRAINTS命令检查。缺省是NOT DEFERRABLE。目前， UNIQUE约束、主键约束、外键约束可以接受这个子句。所有其他约束类型都是不可推迟的。

● INITIALLY IMMEDIATE | INITIALLY DEFERRED

如果约束是可推迟的，则这个子句声明检查约束的缺省时间。– 如果约束是INITIALLY IMMEDIATE（缺省），则在每条语句执行之后就立即检查它。– 如果约束是INITIALLY DEFERRED ，则只有在事务结尾才检查它。约束检查的时间可以用SET CONSTRAINTS命令修改。

1.1.2 分布式

CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ]table_name({ column_name data_type [ compress_mode ] [ CHARACTER SET | CHARSET charset ] [ COLLATEcollation ] [ column_constraint [ ... ] ]| table_constraint| LIKE source_table [ like_option [...] ] }[, ... ])[ table_option [ [ , ] ... ] ][ WITH ( {storage_parameter = value} [, ... ] ) ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ][ COMPRESS | NOCOMPRESS ][ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year }OF { NO MODIFICATION } [ ON ( EXPR )]][ TABLESPACE tablespace_name ][ DISTRIBUTE BY { REPLICATION | HASH ( column_name [, ...] )| RANGE ( column_name [, ...] ) { SLICE REFERENCES tablename | ( slice_less_than_item [, ...] )| ( slice_start_end_item [, ...] ) }| LIST ( column_name [, ...] ) { SLICE REFERENCES tablename | ( slice_values_item [, ...] ) }} ][ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

– 其中列约束column_constraint为：

[ CONSTRAINT constraint_name ]{ NOT NULL |NULL |CHECK ( expression ) |COMMENT 'string' |REFERENCES reftableDEFAULT default_expr |ON UPDATE update_expr |UNIQUE [KEY] [ index_parameters ] |PRIMARY KEY [ index_parameters ]}REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ][ ON DELETE action ] [ ON UPDATE action ] }[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

– 其中表约束table_constraint为：

[ CONSTRAINT [constraint_name] ]{ CHECK ( expression ) |UNIQUE [ index_name ][ USING method ] ( { {column_name | ( expression ) } [ ASC | DESC ] }[, ... ] ) [ index_parameters ] |PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] )[ index_parameters ] |PARTIAL CLUSTER KEY ( column_name [, ... ] ) }[ DEFERRABLE | NOT DEFERRABLE ][ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]{ [ COMMENT 'string' ] [ ... ] }

● CONSTRAINT [constraint_name]

列约束或表约束的名称。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。定义约束有两种方法：– 列约束：作为一个列定义的一部分，仅影响该列。– 表约束：不和某个列绑在一起，可以作用于多个列。

● NOT NULL

字段值不允许为NULL。

● CHECK ( expression )

CHECK约束声明一个布尔表达式，每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功，否则会抛出一个异常并且不会修改数据库。声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。

● UNIQUE [KEY] index_parameters

UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。对于唯一约束， NULL被认为是互不相等的。UNIQUE KEY仅在MYSQL模式数据库下（即sql_compatibility = 'MYSQL'）支持使用，与UNIQUE语义相同。

● UNIQUE [ index_name ][ USING method ] ( { {column_name |( expression ) } [ ASC | DESC ] }[, ... ] ) index_parametersUNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。对于唯一约束， NULL被认为是互不相等的。index_name为索引名。

● PRIMARY KEY index_parameters

PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。一个表只能声明一个主键。

● REFERENCES

当前版本分布式数据库暂不支持REFERENCES子句。

● DEFERRABLE | NOT DEFERRABLE

这两个关键字设置该约束是否可推迟。一个不可推迟的约束将在每条命令之后马上检查。可推迟约束可以推迟到事务结尾使用SET CONSTRAINTS命令检查。缺省是NOT DEFERRABLE。目前， UNIQUE约束和主键约束可以接受这个子句。所有其他约束类型都是不可推迟的。

● INITIALLY IMMEDIATE | INITIALLY DEFERRED

如果约束是可推迟的，则这个子句声明检查约束的缺省时间。– 如果约束是INITIALLY IMMEDIATE（缺省），则在每条语句执行之后就立即检查它；– 如果约束是INITIALLY DEFERRED ，则只有在事务结尾才检查它。   约束检查的时间可以用SET CONSTRAINTS命令修改。

● USING INDEX TABLESPACE tablespace_name

为UNIQUE或PRIMARY KEY约束相关的索引声明一个表空间。如果没有提供这个子句，这个索引将在default_tablespace中创建，如果default_tablespace为空，将使用数据库的缺省表空间。

1.2 建表添加约束示例

1.2.1 检查约束

关键字CHECK给字段添加检查约束，在检查约束中必须引用表中的一个或多个字段，并且表达式返回结果必须是一个布尔值。在表达式中不能包含子查询。对同一个字段可以同时定义检查约束和非空约束。

CREATE TABLE products (    product_no integer,    name text,    price numeric CONSTRAINT positive_price CHECK (price > 0));INSERT INTO products VALUES(1,'a',11);违反约束：INSERT INTO products VALUES(1,'a',-1);

1.2.2 非空约束

非空约束的字段，如果在添加数据时没有指定值，就会报错。可以为表中多个字段添加非空约束。

CREATE TABLE products2 (    product_no integer NOT NULL,    name text NOT NULL,    price numeric NOT NULL CHECK (price > 0));INSERT INTO products2 VALUES(1,'a',11);违反约束：INSERT INTO products2 VALUES(1,'',11);INSERT INTO products2 VALUES(1,null,11);

1.2.3 唯一约束

关键字UNIQUE给字段添加一个唯一约束，插入数据时该字段如有重复则触发约束，多个NULL不算重复，添加唯一约束时，会自动增加一个唯一索引。可以为表中多个字段添加唯一约束。

CREATE TABLE products3 (    product_no integer CONSTRAINT must_be_different UNIQUE,    name text,    price numeric);INSERT INTO products3 VALUES(1,'a',11);INSERT INTO products3 VALUES('','a',11);INSERT INTO products3 VALUES(null,'a',11);违反约束：INSERT INTO products3 VALUES(1,'b',12);

1.2.4 主键约束

关键字PRIMARY KEY给字段添加唯一约束，要求字段唯一且不为空。添加主键约束时自动为该表创建唯一索引，也会为该字段自动增加一个非空约束。

每个表里面只能定义一个主键约束，不能定义多个。

CREATE TABLE products4 (    product_no integer PRIMARY KEY,    name text,    price numeric);INSERT INTO products4 VALUES(1,'a',11);违反约束：INSERT INTO products4 VALUES(1,'b',12);INSERT INTO products4 VALUES('','c',13);INSERT INTO products4 VALUES(null,'c',13);

1.2.5 外键约束

当两个表包含一个或多个公共列时，可以通过外键约束来强制两个表之间的关系。– FOREIGN KEY：用来指定该表中和被引用的表有关系的字段。– REFERENCES：用来指定被引用的表和原表有关系的字段。

外键约束的特点：

定义为外键约束的字段中只能包含相应的其他表中引用字段的值或NULL。

可以为一个字段或者多个字段定义外键约束。

定义了外键约束的字段和相应的引用字段可以存在同一个表中，称为自引用。

对同一个字段可以同时定义外键和非空约束。主表中被应用的列，必须有主键约束或唯一约束。

1.2.5.1 主备式

CREATE TABLE orders (    order_id integer PRIMARY KEY,    product_no integer REFERENCES products4 (product_no),    quantity integer);select * from products4;INSERT INTO orders VALUES(1,1,20);违反约束：INSERT INTO orders VALUES(2,2,21);

1.2.5.2 分布式

目前版本分布式数据库不支持外键约束！

CREATE TABLE orders (    order_id integer PRIMARY KEY,    product_no integer REFERENCES products4 (product_no),    quantity integer);CREATE TABLE orders2 (    order_id integer PRIMARY KEY,    product_no integer,    quantity integer,    CONSTRAINT "orders_product4_fkey" FOREIGN KEY ("product_no") REFERENCES "products4" ("product_no") );

参见：

云数据库 GaussDB 8.1 分布式版开发指南.pdf----P1459

增加或移除约束

增加约束

CREATE TABLE products5 (    product_no integer,    name text,    price numeric,    comment text);INSERT INTO products5 VALUES(1,'a',11);ALTER TABLE products5 ADD CHECK (name <> 'a');ALTER TABLE products5 ADD CHECK (name <> 'b');INSERT INTO products5 VALUES(2,'b',12);INSERT INTO products5 VALUES(2,'c',12);ALTER TABLE products5 ADD UNIQUE (product_no);INSERT INTO products5 VALUES(1,'a',12);INSERT INTO products5 VALUES(3,'d',13);ALTER TABLE products5 ADD PRIMARY KEY (price);ALTER TABLE products5 ADD PRIMARY KEY (product_no);INSERT INTO products5 VALUES(1,'e',14);INSERT INTO products5 VALUES('','f',14);INSERT INTO products5 VALUES(null,'f',14);INSERT INTO products5 VALUES(4,'e',14);

对于分布式数据库，主键约束必须包含分布列！

要增加一个不能写成表约束的非空约束，可使用语法：

ALTER TABLE products5 ALTER COLUMN price SET NOT NULL;INSERT INTO products5 VALUES(5,'f','');INSERT INTO products5 VALUES(5,'f',null);INSERT INTO products5 VALUES(5,'f',11);

该约束会立即被检查，所以表中的数据必须在约束被增加之前就已经符合约束。

移除约束

为了移除一个约束首先需要知道它的名称。如果在创建时已经给它指定了名称，那么事情就变得很容易。否则约束的名称是由系统生成的，我们必须先找出这个名称。psql的命令\d *表名*将会对此有所帮助，其他接口也会提供方法来查看表的细节。因此命令是：

\d+ products5ALTER TABLE products5 DROP CONSTRAINT products5_pkey;ALTER TABLE products5 DROP CONSTRAINT products5_product_no_key;ALTER TABLE products5 DROP CONSTRAINT products5_name_check;

（如果处理的是自动生成的约束名称，如$2，别忘了用双引号使它变成一个合法的标识符。）

和移除一个列相似，如果需要移除一个被某些别的东西依赖的约束，也需要加上CASCADE。一个例子是一个外键约束依赖于被引用列上的一个唯一或者主键约束。

这对除了非空约束之外的所有约束类型都一样有效。为了移除一个非空约束可以用：

ALTER TABLE products5 ALTER COLUMN product_no DROP NOT NULL;ALTER TABLE products5 ALTER COLUMN price DROP NOT NULL;\d+ products5

（回忆一下，非空约束是没有名称的，所以不能用第一种方式。）

约束的查看

元命令查看表属性

数据库管理软件查看建表语句

参考文档

云数据库 GaussDB 8.102 主备版开发者指南.pdf----P1509

云数据库 GaussDB 8.1 分布式版开发指南.pdf----P1461