表的约束(二)
专栏内容:
- postgresql内核源码分析
- 手写数据库toadb
- 并发编程
个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.
一、概述
在数据库中,数据类型可以限制数据存储的大小,也能在一定程度上限制存储的数据种类,但是对于数据库应用来讲,它太宽泛了,比如有些表示人名的字段,就不能为空,货物数量的字段,不能为负值,这与实际生活符合,而数据类型并不能做这些约束,这就需要数据库提供一套更贴近应用,或者说与现实世界更符合的规则,来约束数据的有效性。
约束的定义
数据库的约束是一种规则,用于限制或规范数据库中的数据,确保数据的完整性和一致性。这些约束可以定义在表级别或列级别,处理机制是一致的。约束不占用任何数据库空间,而是存在于数据字典中,并在执行SQL期间使用。用户可以指明约束是启用的还是禁用的,当约束启用时,它增强了数据的完整性。
约束的分类
postgresql数据库中的约束类型主要包括以下几种:
- 主键约束(Primary Key):主键约束相当于唯一约束和非空约束的组合。它确保表中的每一行数据都有一个唯一标识符,不允许重复,也不允许出现空值。每个表最多只允许一个主键。当创建主键约束时,系统默认会在所在的列或列组合上建立对应的唯一索引。
- 外键约束(Foreign Key):外键约束用于保证一个或两个表之间的参照完整性。它构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
- 唯一约束(Unique):唯一约束要求指定的表列或列组合的值不能重复,保证数据的唯一性。同一个表可以有多个唯一约束。在创建唯一约束时,如果不给唯一约束名称,就默认和列名相同。与主键约束不同,唯一约束允许空值的存在,但只能出现一个空值。
- 非空约束(NOT NULL):非空约束用于确保当前列的值不为空值。它只能出现在表对象的列上。
- 检查约束(Check):检查约束会判断指定的条件是否为true,当为true时符合约束。
- 排除约束(Exclude): 在排除约束指定的表达式返回false或null时,才符合约束。
这些约束在数据库设计中起着至关重要的作用,它们有助于维护数据的准确性和一致性,防止无效数据的插入和更新。
二、唯一约束
唯一约束来限制当前列中不能出现重复值,在postgresql中,创建唯一约束时会自动创建一个唯一性索引在对应的列上,通过唯一性索引来提高检查重复值的效率。
添加唯一约束的SQL语法如下:
CREATE TABLE table_name (
...
column1 data_type UNIQUE,
...
);
也可以在定义的尾部,给多个列为一组添加唯一约束
CREATE TABLE table_name (
...
column1 data_type ,
column2 data_type ,
...
UNIQUE (column1, column2)
);
当表已经创建时,可以通过修改表的方式来添加,但是这里与其它不同之处是,有两种方式,一种是直接添加唯一性约束,当然会自动创建唯一性索引;另一种是先创建唯一性索引,再将索引以唯一性约束的形式应用到表上。
我们这里演示一下第一种方式
下面演示一下,创建一张表。
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)
创建了一张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)
可以看到添加成功,索束的名称由数据库自动生成。
插入数据试验一下。
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.
插入两条数据,在第二条数据中,字段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)
再插入两条数据,而字段price
的值并没有赋值,也就是null值,可以看到都插入成功了,这就是null含义,未知,它与任何值都不相等。
第二种方式式的SQL语法示例如下:
- 先要创建一个唯一性索引
CREATE UNIQUE INDEX unique_index_name
ON table_name (column1,...);
- 然后将表的列应用此唯一性索引
ALTER TABLE table_name
ADD CONSTRAINT unique_constraint_name
UNIQUE USING INDEX unique_index_name;
这里涉及到三个名字,第一个是表名,第二个是唯一性约束的名称,第三个是前一步创建的唯一性索引的名称。
在第一步创建完索引后,其实通过索引已经实现不能有重复值出现,但是查看表定义时,并不是以约束的形式出现,直到第二步添加约束后,可以看到表上出现了唯一性约束。
特别说明的是,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
先删除表,重新创建一下,仍然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.
然后同样插入两条price字段为null的数据,可以看到确实null值也不能重复了。
三、非空约束
NULL在数据库中是一个特殊值,表示不知道、没有任何信息,它不等于任何值,包括它自己。而在我们现实生活中,有些信息是必须要存在,比如商品的编号或名称,如何不存在,那就不能唯一标识这种商品,也就意味着这种商品不存在。
为了约束这种情况的发生,就增加一种not null的约束,它的SQL语法如下:
CREATE TABLE table_name(
...
column1 data_type NOT NULL,
...
);
在某一列定义后加not null
关键字。
当然它也可以通过修改表的方式来添加,SQL语法如下:
ALTER TABLE table_name
ALTER COLUMN column1 SET NOT NULL;
定义了主键,同时在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)
可以看到在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),
...
);
其中可以用关键字CONSTRAINT
来指定一个约束的名称,如果不指定名称,数据库会自动生成一个名称。
也可以将check约束单独放为一行定义,它可能会涉及多行,尽量与它关联的行靠近定义。
CREATE TABLE table_name(
column1 datatype,
...,
column2 datatype,
CONSTRAINT check_constraint_name CHECK(condition_expression1),
...,
column3 datatype,
CHECK(condition_expression2)
...
);
也可以通过修改表的定义来添加,
ALTER TABLE table_name
ADD CONSTRAINT check_constraint_name CHECK (condition_expression1);
下面我们来演示一下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
在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
创建一个记录圆形的表,增加约束限制,两个圆不能有重叠,也就是在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
圆的输入是圆心坐标 + 半径, 插入一条以(0,1)圆心,半径为5的圆的数据,再次插入以(0,2)为圆心半径为5的圆时,它们会重叠,违反排除约束。
而我们再次插入一条以(0,12)为圆心半径为5的圆时,没有与它重叠的图形,可以插入成功。
六、总结
本章节介绍了在postgresql中的几种约束,unique唯一性约束,not null非空约束,check条件检查约束,还有exclusion排除约束,通过原理介绍,并在一些案例中进行实践来加深理解,当然它们都可以组合使用,有效利用约束可以使我们的数据更加有效和完整。
七、结尾
非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!
作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。文章来源:https://www.toymoban.com/news/detail-844276.html
注:未经同意,不得转载!文章来源地址https://www.toymoban.com/news/detail-844276.html
到了这里,关于【postgresql 基础入门】表的约束(二) 唯一unique、非空not null、check、exclusion约束,原理机制以及多列组合约束的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!