Postgresql 简明教程

PostgreSQL - CONSTRAINTS

约束是对表中的数据列强制实施的规则。这些用于防止无效数据输入到数据库中。这确保了数据库中数据的准确性和可靠性。

Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database.

约束可以是列级别或表级别。列级别约束仅应用于一列，而表级别约束应用于整个表。为列定义数据类型本身就是一种约束。例如，DATE 类型的列将列约束为有效日期。

Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table. Defining a data type for a column is a constraint in itself. For example, a column of type DATE constrains the column to valid dates.

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

The following are commonly used constraints available in PostgreSQL.

  1. NOT NULL Constraint − Ensures that a column cannot have NULL value.

  2. UNIQUE Constraint − Ensures that all values in a column are different.

  3. PRIMARY Key − Uniquely identifies each row/record in a database table.

  4. FOREIGN Key − Constrains data based on columns in other tables.

  5. CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions.

  6. EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.

NOT NULL Constraint

默认情况下，一列可以容纳 NULL 值。如果您不希望一列具有 NULL 值，则需要在此列上定义此类约束，并指定此列不再允许 NULL。NOT NULL 约束始终写为列约束。

By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column. A NOT NULL constraint is always written as a column constraint.

NULL 与无数据并不相同；相反，它表示未知数据。

A NULL is not the same as no data; rather, it represents unknown data.

Example

例如，以下 PostgreSQL 语句创建了一个名为 COMPANY1 的新表并添加了五列，其中三列（ID、NAME 和 AGE）指定不接受 NULL 值 −

For example, the following PostgreSQL statement creates a new table called COMPANY1 and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULL values −

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

UNIQUE Constraint

UNIQUE 约束防止两条记录在特定列中具有相同的值。例如，在 COMPANY 表中，您可能希望防止两个人或更多人具有相同的年龄。

The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having identical age.

Example

例如，以下 PostgreSQL 语句创建了一个名为 COMPANY3 的新表并添加了五列。在此，AGE 列设置为 UNIQUE，因此您不能具有两条具有相同年龄的记录 −

For example, the following PostgreSQL statement creates a new table called COMPANY3 and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with same age −

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
);

PRIMARY KEY Constraint

PRIMARY KEY 约束唯一标识数据库表中的每条记录。可以有更多 UNIQUE 列，但在表中只能有一个主键。在设计数据库表时，主键很重要。主键是唯一 ID。

The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique ids.

我们用它们来指代表格行。创建表格之间的关系时，主键会成为其他表格中的外键。由于“长期的编码监督”，在 SQLite 中主键可以为 NULL。其他数据库中并非如此。

We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a 'longstanding coding oversight', primary keys can be NULL in SQLite. This is not the case with other databases

主键是表格中的一个字段，它唯一地标识数据库表格中的每一行/记录。主键必须包含唯一的值。主键列不能有 NULL 值。

A primary key is a field in a table, which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

一个表只能有一个主键，它可以由单个或多个字段组成。当多个字段用作主键时，它们被称为 composite key 。

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

如果表在任何字段上定义了主键，那么你不能让两条记录具有该字段的相同值。

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Example

您在上面已经看到各种我们用主键创建 COMAPNY4 表格的示例 −

You already have seen various examples above where we have created COMAPNY4 table with ID as primary key −

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

FOREIGN KEY Constraint

外键约束指定列（或一组列）中的值必须与另一表格的其他行中出现的某个值匹配。我们称之为维护两个相关表格之间的引用完整性。它们被称为外键，因为这些约束是外来的，也就是说，在表格之外。外键有时称为引用键。

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables. They are called foreign keys because the constraints are foreign; that is, outside the table. Foreign keys are sometimes called a referencing key.

Example

例如，以下 PostgreSQL 语句创建一个名为 COMPANY5 的新表格，并添加五列。

For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns.

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

例如，以下 PostgreSQL 语句创建一个名为 DEPARTMENT1 的新表格，并添加三列。EMP_ID 列是外键，它引用 COMPANY6 表格的 ID 字段。

For example, the following PostgreSQL statement creates a new table called DEPARTMENT1, which adds three columns. The column EMP_ID is the foreign key and references the ID field of the table COMPANY6.

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

CHECK Constraint

检查约束允许检查要输入到记录中的条件。如果条件为假，记录则违反约束，不会被输入到表格中。

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and is not entered into the table.

Example

例如，以下 PostgreSQL 语句创建一个名为 COMPANY5 的新表格，并添加五列。此处，我们在 SALARY 列中添加一个 CHECK，以便您无法将任何 SALARY 设为零。

For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY as Zero.

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)
);

EXCLUSION Constraint

排除约束确保：如果在指定列或使用指定运算符的表达式中对任意两行进行比较，那么这些运算符比较中至少会有一个返回 false 或 null。

Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

Example

例如，以下 PostgreSQL 语句创建一个名为 COMPANY7 的新表格，并添加五列。此处，我们添加一个 EXCLUDE 约束 −

For example, the following PostgreSQL statement creates a new table called COMPANY7 and adds five columns. Here, we add an EXCLUDE constraint −

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)
);

其中，USING gist 是要构建和用于强制的索引类型。

Here, USING gist is the type of index to build and use for enforcement.

由于我们强制年龄必须相同，我们通过向表格中插入记录来了解它 −

As we have enforced the age has to be same, let us see this by inserting records to the table −

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );

对于前两个 INSERT 语句，记录被添加到 COMPANY7 表格。对于第三个 INSERT 语句，显示以下错误 −

For the first two INSERT statements, the records are added to the COMPANY7 table. For the third INSERT statement, the following error is displayed −

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

Dropping Constraints

为移除约束，您需要知道其名称。如果知道了该名称，则很容易删除。否则，您需要找出系统生成的名称。此处可以使用 psql 命令 \d table name。一般语法为 −

To remove a constraint you need to know its name. If the name is known, it is easy to drop. Else, you need to find out the system-generated name. The psql command \d table name can be helpful here. The general syntax is −

ALTER TABLE table_name DROP CONSTRAINT some_name;