Sql 简明教程

SQL - Constraints

SQL Constraints

SQL 约束是应用于数据列或完整表的规则，用于限制可以添加到表中的数据的类型。当您尝试对表执行任何 INSERT、UPDATE 或 DELETE 操作时，RDBMS 会检查该数据是否违反任何现有约束，如果定义的约束与数据操作之间有任何违反，则会中止操作并返回错误。

SQL Constraints are the rules applied to a data columns or the complete table to limit the type of data that can go into a table. When you try to perform any INSERT, UPDATE, or DELETE operation on the table, RDBMS will check whether that data violates any existing constraints and if there is any violation between the defined constraint and the data action, it aborts the operation and returns an error.

我们可以定义列级别或表级别约束。列级别约束仅应用于一列，而表级别约束应用于整个表。

We can define a column level or a table level constraints. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.

SQL Create Constraints

我们可以在使用 CREATE TABLE 语句创建表时定义表上的约束，或者在创建表之后，我们可以使用 ALTER TABLE 语句来创建或删除表约束。

We can create constraints on a table at the time of a table creation using the CREATE TABLE statement, or after the table is created, we can use the ALTER TABLE statement to create or delete table constraints.

CREATE TABLE table_name (
   column1 datatype constraint,
   column2 datatype constraint,
   ....
   columnN datatype constraint
);

不同的 RDBMS 允许定义不同的约束。本教程将讨论 MySQL 中 7 个最重要约束。

Different RDBMS allows to define different constraints. This tutorial will discuss about 7 most important constraints available in MySQL.

NOT NULL Constraint

当应用于列时，NOT NULL 约束确保列不能拥有 NULL 值。以下是创建 NOT NULL 约束的示例：

When applied to a column, NOT NULL constraint ensure that a column cannot have a NULL value. Following is the example to create a NOT NULL constraint:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

在 NOT NULL Constraint 上查看详细信息

Check further detail on NOT NULL Constraint

UNIQUE Key Constraint

当应用于列时，UNIQUE Key 约束确保列仅接受 UNIQUE 值。以下是针对列 ID 创建 UNIQUE Key 约束的示例。此约束创建后，ID 列不能为 null，并且它只会接受 UNIQUE 值。

When applied to a column, UNIQUE Key constraint ensure that a column accepts only UNIQUE values. Following is the example to create a UNIQUE Key constraint on column ID. Once this constraint is created, column ID can’t be null and it will accept only UNIQUE values.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL UNIQUE,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

在 Unique Key Constraint 上查看详细信息

Check further detail on Unique Key Constraint

DEFAULT Value Constraint

当应用于列时，DEFAULT Value 约束在未指定时为列提供一个默认值。以下是针对列 NAME 创建 DEFAULT 约束的示例。此约束创建后，如果 NAME 未设置为某个值，NAME 列将设置为 "Not Available" 值。

When applied to a column, DEFAULT Value constraint provides a default value for a column when none is specified. Following is the example to create a DEFAULT constraint on column NAME. Once this constraint is created, column NAME will set to "Not Available" value if NAME is not set to a value.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL UNIQUE,
   NAME VARCHAR (20) DEFAULT 'Not Available',
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

在 DEFAULT Value Constraint 上查看详细信息

Check further detail on DEFAULT Value Constraint

PRIMARY Key Constraint

当应用于列时，PRIMARY Key 约束确保列仅接收 UNIQUE 值，表上可能只有一个 PRIMARY Key，但多个列可以构成一个 PRIMARY Key。以下是针对列 ID 创建 PRIMARY Key 约束的示例。此约束创建后，列 ID 不能为空，并且它只会接受唯一的值。

When applied to a column, PRIMARY Key constraint ensure that a column accepts only UNIQUE value and there can be a single PRIMARY Key on a table but multiple columns can constituet a PRIMARY Key. Following is the example to create a PRIMARY Key constraint on column ID. Once this constraint is created, column ID can’t be null and it will accept only unique values.

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

在 PRIMARY Key Constraint 上查看详细信息

Check further detail on PRIMARY Key Constraint

FOREIGN Key Constraint

FOREIGN Key 约束与另一表中的一列映射，并唯一标识该表中一行/记录。以下是一个示例，用于针对 CUSTOMERS 表中列 ID 创建外键约束，如下面的语句所示 −

FOREIGN Key constraint maps with a column in another table and uniquely identifies a row/record in that table. Following is an example to create a foreign key constraint on column ID available in CUSTOMERS table as shown in the statement below −

CREATE TABLE ORDERS (
   ID INT NOT NULL,
   DATE DATETIME,
   CUSTOMER_ID INT FOREIGN KEY REFERENCES CUSTOMERS(ID),
   AMOUNT DECIMAL,
   PRIMARY KEY (ID)
);

有关 FOREIGN Key Constraint 的详细信息，请查看

Check further detail on FOREIGN Key Constraint

CHECK Value Constraint

CHECK 值约束应用于数据表时，就像是一个验证，用来验证输入到某数据表某列的数据的有效性。表并唯一标识表中的某行/记录。以下举例说明在 AGE 列上创建 CHECK 验证，不接受其值低于 18 的情况。

When applied to a column, CHECK Value constraint works like a validation and it is used to check the validity of the data entered into the particular column of the table. table and uniquely identifies a row/record in that table. Following is an example to create a CHECK validation on AGE column which will not accept if its value is below to 18.

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL CHECK(AGE>=18),
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

有关 CHECK Value Constraint 的详细信息，请查看

Check further detail on CHECK Value Constraint

INDEX Constraint

创建 INDEX 约束是为了加快从数据库中检索数据。可以通过使用数据表中的一列或若干列来创建索引。表可以具有一个 PRIMARY 键，但可以具有多个 INDEX。索引可以根据要求是唯一索引或非唯一索引。以下举例说明在 CUSTOMERS 表的 Age 列上创建索引。

The INDEX constraints are created to speed up the data retrieval from the database. An Index can be created by using a single or group of columns in a table. A table can have a single PRIMARY Key but can have multiple INDEXES. An Index can be Unique or Non Unique based on requirements. Following is an example to create an Index on Age Column of the CUSTOMERS table.

CREATE INDEX idx_age ON CUSTOMERS ( AGE );

有关 INDEX Constraint 的详细信息，请查看

Check further detail on INDEX Constraint

Dropping SQL Constraints

可以通过带有 DROP CONSTRAINT 选项的 ALTER TABLE 命令来删除您已定义的任何约束。例如，要从 CUSTOMERS 表中删除主键约束，您可以使用以下命令。

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option. For example, to drop the primary key constraint from the CUSTOMERS table, you can use the following command.

ALTER TABLE CUSTOMERS DROP CONSTRAINT PRIMARY KEY;

有些 RDBMS 允许您禁用约束，而不是永久从数据库中删除它们，这样您就可以暂时禁用这些约束，然后在以后启用它们。

Some RDBMS allow you to disable constraints instead of permanently dropping them from the database, which you may want to temporarily disable the constraints and then enable them later.

Data Integrity Constraints

数据完整性约束用于确保数据的整体准确性、完整性和一致性。现在，数据完整性还指与法规相符性有关的数据安全性，例如 GDPR 合规性等。

Data integrity constraints are used to ensure the overall accuracy, completeness, and consistency of data. Now a days data integrity also refers to the data safety in regard to regulatory compliance, such as GDPR compliance etc.

在关系数据库中，通过参照完整性的概念来处理数据完整性。有许多类型的完整性约束在 Referential Integrity (RI) 中发挥作用。这些约束包括主键、外键、唯一约束以及上面提到的其他约束。

Data integrity is handled in a relational database through the concept of referential integrity. There are many types of integrity constraints that play a role in Referential Integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints which are mentioned above.