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

这篇具有很好参考价值的文章主要介绍了【postgresql 基础入门】表的约束(二) 唯一unique、非空not null、check、exclusion约束,原理机制以及多列组合约束。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

表的约束(二)

专栏内容

  • 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,
  ...
);

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

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

到了这里,关于【postgresql 基础入门】表的约束(二) 唯一unique、非空not null、check、exclusion约束,原理机制以及多列组合约束的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包