8.PostgreSQL约束

不念不忘少年蓝@ 2022-10-08 12:24 80阅读 0赞

# 一、PostgreSQL 约束 #

PostgreSQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

约束确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

以下是在 PostgreSQL 中常用的约束。

*  **NOT NULL**：指示某列不能存储 NULL 值。
 *  **UNIQUE**：确保某列的值都是唯一的。
 *  PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。。
 *  FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。
 *  CHECK： 保证列中的值符合指定的条件。
 *  EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。

## 1.1 NOT NULL 约束 ##

默认情况下，列可以保存为 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。

NULL 与没有数据是不一样的，它代表着未知的数据。

**实例**

下面实例创建了一张新表叫 COMPANY1，添加了 5 个字段，其中三个 ID，NAME，AGE 设置不接受空置：

CREATE TABLE COMPANY1(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL
    );

## 1.2 UNIQUE 约束 ##

UNIQUE 约束可以设置列是唯一的，避免同一列出现重复值。

**实例**

下面实例创建了一张新表叫 COMPANY3，添加了 5 个字段，其中 AGE 设置为 UNIQUE，因此你不能添加两条有相同年龄的记录：

CREATE TABLE COMPANY3(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL UNIQUE,
       ADDRESS        CHAR(50),
       SALARY         REAL    DEFAULT 50000.00
    );

## 1.3 PRIMARY KEY ##

在设计数据库时，PRIMARY KEY 非常重要。

PRIMARY KEY 称为主键，是数据表中每一条记录的唯一标识。

设置 UNIQUE 的列可能有多个，但是一张表只有一列可以设置 PRIMARY KEY。

我们可以使用主键来引用表中的行，也可以通过把主键设置为其他表的外键，来创建表之间的关系。

主键是非空约束和唯一约束的组合。

一个表只能有一个主键，它可以由一个或多个字段组成，当多个字段作为主键，它们被称为复合键。

如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

**实例**

下面我们创建 COMAPNY4 表，其中 ID 作为主键：

CREATE TABLE COMPANY4(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL
    );

## 1.4 FOREIGN KEY 约束 ##

FOREIGN KEY 即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。

通常一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。

**实例**

下面实例创建了一张 COMPANY6 表，并添加了5个字段：

CREATE TABLE COMPANY6(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL
    );

下面实例创建一张 DEPARTMENT1 表，并添加 3 个字段，EMP\_ID 就是外键，参照 COMPANY6 的 ID：

CREATE TABLE DEPARTMENT1(
       ID INT PRIMARY KEY      NOT NULL,
       DEPT           CHAR(50) NOT NULL,
       EMP_ID         INT      references COMPANY6(ID)
    );

## 1.5 CHECK 约束 ##

CHECK 约束保证列中的所有值满足某一条件，即对输入一条记录要进行检查。如果条件值为 false，则记录违反了约束，且不能输入到表。

**实例**

例如，下面实例建一个新的表 COMPANY5，增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零：

CREATE TABLE COMPANY5(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL    CHECK(SALARY > 0)
    );

## 1.6 EXCLUSION 约束 ##

EXCLUSION 约束确保如果使用指定的运算符在指定列或表达式上比较任意两行，至少其中一个运算符比较将返回 false 或 null。

**实例**

下面实例创建了一张 COMPANY7 表，添加 5 个字段，并且使用了 EXCLUDE 约束。

CREATE TABLE COMPANY7(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT,
       AGE            INT  ,
       ADDRESS        CHAR(50),
       SALARY         REAL,
       EXCLUDE USING gist
       (NAME WITH =,  -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
       AGE WITH <>)   -- 其比较的结果是如果整个表边式返回 true，则不允许插入，否则允许
    );

这里，USING gist 是用于构建和执行的索引一种类型。

> 您需要为每个数据库执行一次 CREATE EXTENSION btree\_gist 命令，这将安装 btree\_gist 扩展，它定义了对纯标量数据类型的 EXCLUDE 约束。

由于我们已经强制执行了年龄必须相同，让我们通过向表插入记录来查看这一点：

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
    INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );  
    -- 此条数据的 NAME 与第一条相同，且 AGE 与第一条也相同，故满足插入条件
    INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
    -- 此数据与上面数据的 NAME 相同，但 AGE 不相同，故不允许插入

前面两条顺利添加的 COMPANY7 表中，但是第三条则会报错：

ERROR:  duplicate key value violates unique constraint "company7_pkey"
    DETAIL:  Key (id)=(3) already exists.

## 1.7 删除约束 ##

删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \\d 表名 可以找到这些信息。

通用语法如下：

ALTER TABLE table_name DROP CONSTRAINT some_name;