【postgresql 基础入门】表的约束(二) 唯一unique、非空not null、check、exclusion约束，原理机制以及多列组合约束_postgresql table unique

表的约束(二)

​专栏内容：

• postgresql内核源码分析
• 手写数据库toadb
• 并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

一、概述

---

在数据库中，数据类型可以限制数据存储的大小，也能在一定程度上限制存储的数据种类，但是对于数据库应用来讲，它太宽泛了，比如有些表示人名的字段，就不能为空，货物数量的字段，不能为负值，这与实际生活符合，而数据类型并不能做这些约束，这就需要数据库提供一套更贴近应用，或者说与现实世界更符合的规则，来约束数据的有效性。

约束的定义

数据库的约束是一种规则，用于限制或规范数据库中的数据，确保数据的完整性和一致性。这些约束可以定义在表级别或列级别，处理机制是一致的。约束不占用任何数据库空间，而是存在于数据字典中，并在执行SQL期间使用。用户可以指明约束是启用的还是禁用的，当约束启用时，它增强了数据的完整性。

约束的分类

postgresql数据库中的约束类型主要包括以下几种：

1. 主键约束（Primary Key）：主键约束相当于唯一约束和非空约束的组合。它确保表中的每一行数据都有一个唯一标识符，不允许重复，也不允许出现空值。每个表最多只允许一个主键。当创建主键约束时，系统默认会在所在的列或列组合上建立对应的唯一索引。
2. 外键约束（Foreign Key）：外键约束用于保证一个或两个表之间的参照完整性。它构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
3. 唯一约束（Unique）：唯一约束要求指定的表列或列组合的值不能重复，保证数据的唯一性。同一个表可以有多个唯一约束。在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。与主键约束不同，唯一约束允许空值的存在，但只能出现一个空值。
4. 非空约束（NOT NULL）：非空约束用于确保当前列的值不为空值。它只能出现在表对象的列上。
5. 检查约束（Check）：检查约束会判断指定的条件是否为true，当为true时符合约束。
6. 排除约束(Exclude): 在排除约束指定的表达式返回false或null时，才符合约束。

这些约束在数据库设计中起着至关重要的作用，它们有助于维护数据的准确性和一致性，防止无效数据的插入和更新。

二、唯一约束

---

唯一约束来限制当前列中不能出现重复值，在postgresql中，创建唯一约束时会自动创建一个唯一性索引在对应的列上，通过唯一性索引来提高检查重复值的效率。

添加唯一约束的SQL语法如下：

CREATE TABLE table_name (
 ...
  column1 data_type UNIQUE,
  ...
);
1
2
3
4
5

也可以在定义的尾部，给多个列为一组添加唯一约束

CREATE TABLE table_name (
 ...
  column1 data_type ,
  column2 data_type ,
  ...
  UNIQUE (column1, column2)
);

1
2
3
4
5
6
7
8

当表已经创建时，可以通过修改表的方式来添加，但是这里与其它不同之处是，有两种方式，一种是直接添加唯一性约束，当然会自动创建唯一性索引；另一种是先创建唯一性索引，再将索引以唯一性约束的形式应用到表上。

我们这里演示一下第一种方式

下面演示一下，创建一张表。

postgres=# drop table products ;
DROP TABLE
postgres=# create table products(product_id int primary key,product_name varchar not null,price double precision);
CREATE TABLE
postgres=# \d products
                      Table "test1.products"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 product_id   | integer           |           | not null |
 product_name | character varying |           | not null |
 price        | double precision  |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
1
2
3
4
5
6
7
8
9
10
11
12
13

创建了一张products表，然后再通过修改表定义的方式添加唯一性索引。

postgres=# alter table products add constraint products_unique_contraint unique (price);
ALTER TABLE
postgres=# \d products
                      Table "test1.products"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 product_id   | integer           |           | not null |
 product_name | character varying |           | not null |
 price        | double precision  |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
    "products_unique_contraint" UNIQUE CONSTRAINT, btree (price)
1
2
3
4
5
6
7
8
9
10
11
12

可以看到添加成功，索束的名称由数据库自动生成。

插入数据试验一下。

postgres=# insert into products values(1,'a',1);
INSERT 0 1
postgres=# insert into products values(2,'b',1);
ERROR:  duplicate key value violates unique constraint "products_unique_contraint"
DETAIL:  Key (price)=(1) already exists.
1
2
3
4
5

插入两条数据，在第二条数据中，字段price与第一条数据相同，可以看到违反了唯一性约束，报错插入不成功。

postgres=# insert into products(product_id,product_name) values(2,'b');
INSERT 0 1
postgres=# insert into products(product_id,product_name) values(3,'c');
INSERT 0 1
postgres=# select * from products ;
 product_id | product_name | price
------------+--------------+-------
          1 | a            |     1
          2 | b            |
          3 | c            |
(3 rows)

1
2
3
4
5
6
7
8
9
10
11
12

再插入两条数据，而字段price的值并没有赋值，也就是null值，可以看到都插入成功了，这就是null含义，未知，它与任何值都不相等。

第二种方式式的SQL语法示例如下：

• 先要创建一个唯一性索引

CREATE UNIQUE INDEX  unique_index_name 
ON table_name (column1,...);
1
2

• 然后将表的列应用此唯一性索引

ALTER TABLE table_name 
ADD CONSTRAINT unique_constraint_name 
UNIQUE USING INDEX unique_index_name;
1
2
3

这里涉及到三个名字，第一个是表名，第二个是唯一性约束的名称，第三个是前一步创建的唯一性索引的名称。

在第一步创建完索引后，其实通过索引已经实现不能有重复值出现，但是查看表定义时，并不是以约束的形式出现，直到第二步添加约束后，可以看到表上出现了唯一性约束。

特别说明的是，null是一个特殊性，null与null并不相等，在有唯一性约束的列上可能出现多行数据都是null的情况，如果要改变这种默认行为，可以在unique关键字后面再追加NULLS NOT DISTINCT。

增加了NULLS NOT DISTINCT关键字后，null值也不允许重复了，下面来演示一下。

postgres=# drop table products ;
DROP TABLE
postgres=# create table products(product_id int primary key,product_name varchar not null,price double precision unique NULLS NOT DISTINCT);
CREATE TABLE
1
2
3
4

先删除表，重新创建一下，仍然price字段创建唯一性约束，同时增加nulls not distinct字段。

postgres=# insert into products(product_id,product_name) values(2,'b');
INSERT 0 1
postgres=# insert into products(product_id,product_name) values(3,'c');
ERROR:  duplicate key value violates unique constraint "products_price_key"
DETAIL:  Key (price)=(null) already exists.
1
2
3
4
5

然后同样插入两条price字段为null的数据，可以看到确实null值也不能重复了。

三、非空约束

---

NULL在数据库中是一个特殊值，表示不知道、没有任何信息，它不等于任何值，包括它自己。而在我们现实生活中，有些信息是必须要存在，比如商品的编号或名称，如何不存在，那就不能唯一标识这种商品，也就意味着这种商品不存在。

为了约束这种情况的发生，就增加一种not null的约束，它的SQL语法如下：

CREATE TABLE table_name(
   ...
   column1 data_type NOT NULL,
   ...
);
1
2
3
4
5

在某一列定义后加not null关键字。

当然它也可以通过修改表的方式来添加，SQL语法如下：

ALTER TABLE table_name
ALTER COLUMN column1 SET NOT NULL;
1
2

定义了主键，同时在product_name列上定义了not null约束。

查看表的定义信息，

postgres=# \d products
                      Table "test1.products"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 product_id   | integer           |           | not null |
 product_name | character varying |           | not null |
 price        | double precision  |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)

1
2
3
4
5
6
7
8
9
10

可以看到在Nullable是否为空列中，有两个字段都是not null约束，主键默认是带有非空约束的。

四、检查约束

---

check检查约束，通过一个结果为布尔值的表达式来检查字段数据的有效性，只有表达式结果为true时才能被insert或update，这一约束保证了数据的实体完整性。

check约束可以在创建表时添加，SQL语法形式如下：

可以在列的定义后面追加check约束，它表明只针对本列的约束

CREATE TABLE table_name(
   column1 datatype CHECK(condition_expression1),
   column2 datatype CONSTRAINT check_constraint_name CHECK(condition_expression2),
   ...
);
1
2
3
4
5

其中可以用关键字CONSTRAINT来指定一个约束的名称，如果不指定名称，数据库会自动生成一个名称。

也可以将check约束单独放为一行定义，它可能会涉及多行，尽量与它关联的行靠近定义。

CREATE TABLE table_name(
   column1 datatype,
   ...,
   column2 datatype,
   CONSTRAINT check_constraint_name CHECK(condition_expression1),
   ...,
   column3 datatype,
   CHECK(condition_expression2)
   ...
);
1
2
3
4
5
6
7
8
9
10

也可以通过修改表的定义来添加，

ALTER TABLE table_name
ADD CONSTRAINT check_constraint_name CHECK (condition_expression1);
1
2

下面我们来演示一下check约束，

postgres=# drop table products ;
DROP TABLE
postgres=# CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);
CREATE TABLE
1
2
3
4
5
6
7
8
9
10
11
12

在price,discounted_price检查，它们都需要大于0，同时打折价discounted_price必须要小于原价price,不能亏本吧。

注意，check约束尽量是简单的表达式，它不能区分update中修改前与修改后的值，另外也不包括自定义的function在表达式中，因为它的值是不确定的。

五、排除约束

---

exclusion约束，与check约束正好相反，它也是指定一个表达式和操作符，如果表达式与操作符结果返回false或null时，就可以插入或更新。

exclusion约束是通过在指定列或几列上创建gin索引来实现快速比较。

它主要用途在集合或图形方面，表达逻辑是两行数据表示的集合或图形不包含，不相交。

下面来简单演示一个例子。

postgres=# CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);
CREATE TABLE

1
2
3
4
5
6

创建一个记录圆形的表，增加约束限制，两个圆不能有重叠，也就是在c列上指定操作符为&&，在检查时会执行运算c1 && c2，如果重叠返回值就为true，会违返exclusion约束。

postgres=# insert into circles values('(0,1),5');
INSERT 0 1
postgres=# select * from circles ;
     c
-----------
 <(0,1),5>
(1 row)

postgres=# insert into circles values('(0,2),5');
ERROR:  conflicting key value violates exclusion constraint "circles_c_excl"
DETAIL:  Key (c)=(<(0,2),5>) conflicts with existing key (c)=(<(0,1),5>).

postgres=# insert into circles values('(0,12),5');
INSERT 0 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

圆的输入是圆心坐标 + 半径, 插入一条以(0,1)圆心，半径为5的圆的数据，再次插入以(0,2)为圆心半径为5的圆时，它们会重叠，违反排除约束。

而我们再次插入一条以(0,12)为圆心半径为5的圆时，没有与它重叠的图形，可以插入成功。

六、总结

---

本章节介绍了在postgresql中的几种约束，unique唯一性约束，not null非空约束，check条件检查约束，还有exclusion排除约束，通过原理介绍，并在一些案例中进行实践来加深理解，当然它们都可以组合使用，有效利用约束可以使我们的数据更加有效和完整。

七、结尾

---

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！