介绍Postgres CHECK约束及其用法

吴欣
577 阅读4分钟

本文介绍了Postgres CHECK约束及其用法。在这篇文章中，我们将学习：

  1. 什么是Postgres的检查约束？
  2. 如何在CREATE TABLE语句中创建一个CHECK约束？
  3. 如何在现有表的某一列上添加一个CHECK约束？
  4. 如何使用pgAdmin4工具创建一个CHECK约束

Postgres检查约束的介绍

在查询向表中插入一条记录之前，CHECK约束会评估CHECK约束定义中指定的条件。如果条件评估为TRUE，那么查询中指定的值将被插入或更新到表中。如果条件评估为FALSE，那么查询将返回一个违反检查条件的错误并终止查询。

在一个新表中创建检查约束条件

我们可以在创建一个新的表时创建一个CHECK约束。为了演示，我创建了一个名为 tblPurchaseOrder.

CREATE TABLE tblpurchaseorder
    (
       orderID      INT,
       Product_Code VARCHAR(500),
       Order_Qty    INT CHECK(order_qty>0),
       Product_Cost NUMERIC(10, 2),
       UserID       VARCHAR(50),
       Orderd_Date  DATE
    );

该表有一个CHECK约束。

  1. CHECK约束检查order_quantity列的值必须大于0。如果一个查询在插入记录时，order_quantity =0，那么它应该抛出一个错误。

现在，让我们尝试添加一条订单数量大于0的记录**。**运行以下查询。

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(1,’PROD0001′,5,500,’nisarg.upadhyay’, ‘2021-05-30’)

image.png

正如你所看到的，记录已被成功添加。让我们试着插入一条订单数量为0的记录。

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(2,’PROD0001′,0,10,’nisarg.upadhyay’, ‘2021-05-30’)
The query has returned an error.

image.png

  • *注意。

    1. 在创建一个新的表时，如果我们定义一个CHECK约束而不指定名称，PostgreSQL会分配一个遵循**[table_name]_[column_name]_check**命名惯例的名称

    2. 我们可以在CREATE TABLE语句中指定约束条件的名称。我们可以在指定CONSTRAINT表达式后指定约束名称。以下是模式：
      创建表tbl_name(col_1 datatype, Col_2 data_type CONSTRAINT constraint_name (Check_condition))

使用ALTER TABLE语句添加约束条件

我们可以通过使用ALTER TABLE语句来添加约束。添加约束条件的语法如下。

ALTER TABLE tbl_name ADD CONSTRAINT constraint_name CHECK (condition)

在这个语法中。

  1. tbl_name：指定包含你想添加CHECK CONSTRAINT的列的表名。
  2. constraint_name：指定需要的约束名称。该约束名称必须在ADD CONSTRAINT表达式之后指定。
  3. 条件：指定CHECK条件。必须在括号内指定CHECK条件。

我们想在tblPurchaseOrder表中的Product_Cost列上添加一个CHECK约束。当我们添加一条Product_Cost<0的记录时，查询必须返回一个错误。运行下面的查询来创建这个约束。

ALTER TABLE tblPurchaseOrder ADD CONSTRAINT “Check_Product_Cost” CHECK (product_cost > 0)

现在，让我们插入一条Product_Cost < 0的记录。

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(2,’PROD0001′,50,0,’nisarg.upadhyay’, ‘2021-05-30’)

image.png

正如你在上面的图片中看到的，查询返回了一个错误。

使用pgAdmin4创建CHECK约束

现在，让我们了解一下如何使用pgAdmin4工具创建一个CHECK约束。pgAdmin4是一个PostgreSQL数据库管理工具。当我们安装PostgreSQL时，pgAdmin4会自动安装。

我们想在tblPurchaseOrder表中的Ordered_date列上添加一个CHECK约束。当我们插入一条order_date>10 天的记录时，查询必须返回一个错误。

要创建一个CHECK约束，启动pgAdmin4。指定主密码来连接到PostgreSQL。

image.png

你可以在浏览器面板上查看已安装的服务器和对象的列表。要查看对象，展开服务器 🡪展开PostgreSQL13 🡪展开数据库。

image.png

右键单击DemoDatabase🡪 展开Schemas🡪 展开public🡪 展开Tables🡪 右键单击tables🡪 选择Properties。

image.png

打开一个配置表属性的对话框。在对话框中，点击约束，在子菜单中，点击检查。点击（+）号，然后点击编辑行按钮。

image.png

另一个子菜单打开。你可以在名称文本框中指定所需的约束条件的名称。

image.png

现在，我们要在tblPurchaseOrder表中创建一个CHECK约束。CHECK约束是两个条件的组合。

  1. 当用户执行INSERT语句时，Orderd_Date的值不应该超过10天。例如，如果今天是2021-05-31，而用户试图插入Orderd_Date的值为2021-05-20，查询必须返回一个错误
  2. Orderd_Date列的值必须不大于当前的日期和时间。

为了满足上述条件，CHECK条件的定义必须是这样的。

CHECK (orderd_date between Current_date-INTEGER ’10’ AND Current_date)

我们可以在定义子菜单中指定CHECK约束的定义。

image.png

点击 "保存"按钮创建约束并关闭对话框。让我们通过插入记录来测试该约束。首先，让我们插入一个记录，其值为Orderd_Date='2021-05-15'。

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(4,’PROD0002′,5,500,’nisarg.upadhyay’, ‘2021-05-15’)

image.png

让我们插入Orderd_Date的值高于Current_date的记录。

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(5,’PROD0002′,1,50,’nisarg.upadhyay’, ‘2021-05-31’)

image.png

查看CHECK限制条件

要查看在表上创建的CHECK约束列表，我们可以使用以下查询方式

select cons.conname as constraint_name,
columnusage.table_schema as table_schema,
columnusage.table_name,
columnusage.column_name
from pg_constraint cons
join pg_namespace namespace on namespace.oid = cons.connamespace
join pg_class class on cons.conrelid = class.oid
left join information_schema.constraint_column_usage columnusage
on cons.conname = columnusage.constraint_name
and namespace.nspname = columnusage.constraint_schema
where contype =’c’
order by cons.conname;

输出

image.png

另外，你可以使用pgAdmin4工具来查看约束。要做到这一点，展开DemoDatabase🡪 ExpandSchemas🡪 Expandpublic🡪 ExpandTables🡪 ExpandConstraints。

image.png

你可以看到，在tblPurchaseOrder表上已经创建了三个约束。

小结

在这篇文章中，我们了解了Postgres的检查约束。我解释了我们如何使用CREATE TABLE语句创建CHECK约束。此外，我们还学习了如何使用ALTER TABLE语句为现有的表添加检查约束。

avatar
文章
阅读
粉丝