Mysql 简明教程

MySQL − Constraints

MySQL Constraints

MySQL constraints 可用于向表中的列设置某些规则。这些约束可以限制可以在特定列中插入或更新的数据类型。这有助于你维护表中的数据准确性和可靠性。

The MySQL constraints can be used to set certain rules to the column(s) in a table. These constraints can restrict the type of data that can be inserted or updated in a particular column. This helps you to maintain the data accuracy and reliability in a table.

MySQL 约束有两种类型。

There are two types of MySQL constraints.

  1. Column level constraints: These type of constraints will only apply to a column in a table.

  2. Table level constraints: These constraints will apply to the complete table.

MySQL 中常用的约束包括 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT、CREATE INDEX、AUTO_INCREMENT 等。

The commonly used constraints in MySQL are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, CREATE INDEX, AUTO_INCREMENT, etc.

Syntax

以下是为表中的列添加约束的基本语法 −

Following is the basic syntax to add a constraint for the column(s) in a table −

CREATE TABLE table_name (
   Column_name1 datatype constraint,
   Column_name2 datatype constraint,
   Column_name3 datatype constraint,
   .........
);

MySQL NOT NULL Constraint

默认情况下，MySQL 表格中的列可以包含 NULL 值。在某些情况下，我们可能希望特定的列不接受或不包含 NULL 值。为此，我们可以使用 MySQL NOT NULL 约束。

By default, a column in a MySQL table can contain NULL values. In some scenarios, we may want a particular column to not accept or contain NULL values. To do so, we can use the MySQL NOT NULL constraint.

此约束强制特定字段始终包含一个值，这意味着我们不能在不为该字段添加一个值的情况下插入或更新记录。

This constraint enforces a specific field to always contain a value, which means that we cannot insert or update a record without adding a value to this field.

Example

在下面的查询中，我们向 ID 和 NAME 列的 CUSTOMERS 表添加 NOT NULL 约束。结果是，在记录插入时， ID 和 NAME 列不会接受 NULL 值。

In the following query, we are adding the NOT NULL constraint on the ID and NAME columns of the CUSTOMERS table. As a result, the ID and NAME columns will not accept NULL values at the time of record insertion.

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int
);

我们尝试向这个表中插入记录。下面的语句会向 CUSTOMERS 表插入一条记录。

Let’s try inserting records into this table. The following statement will insert a record into the CUSTOMERS table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, 'Nikhil', 18);

但是，如果我们尝试将具有空 ID 值的记录插入为：

But, if we try to insert records with NULL values as ID as −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(Null, 'Varun', 26);

则会生成一个错误，提示 “Column 'ID' cannot be null”。

An error will be generated saying "Column 'ID' cannot be null".

ERROR 1048 (23000): Column 'ID' cannot be null

同样，如果我们尝试将 NULL 作为值传递给 NAME 列，将会生成类似的错误。

In the same way if we try to pass NULLs as values to the NAME column, similar error will be generated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(3, Null, 19);

这会生成以下错误：

This will generate the following error −

ERROR 1048 (23000): Column 'NAME' cannot be null

正如我们在上面的查询中所看到的，第一条记录成功插入，因为它在 ID 和 Name 列中没有空值。第二条和第三条记录未插入，因为我们尝试在不应该是 NULL 的列中插入 NULL 值。

As we can see in the above queries, the first record is successfully inserted because it does not have null values in the ID and Name columns. Whereas, the second and third records are not inserted because we are trying to insert NULL values in the columns which shouldn’t be NULL.

MySQL UNIQUE Constraint

MySQL 中的 UNIQUE 约束确保列中的每个值都必须是唯一的。这意味着具有 UNIQUE 约束的列不能有重复的值；每个值必须唯一。

The UNIQUE constraint in MySQL ensures that every value in a column must be distinct. This means the column with the UNIQUE constraint cannot have the same value repeated; each value must be unique.

Note: 对于单个表，我们可能有一个或多个 UNIQUE 约束。

Note: We can have one or more UNIQUE constraints on a single table.

Example

下面的查询在 CUSTOMERS 表的 ID 列上创建一个 UNIQUE 约束：

The following query creates a UNIQUE constraint on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   UNIQUE (ID)
 );

现在，让我们向上面创建的表中插入如下记录：

Now, let us insert the following records into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, 'Nikhil', 18);
INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, 'Varun', 26);

在上面的代码块中，第二个 insert 语句返回一个错误，提示 “Duplicate entry '1' for key 'customers.ID”，因为我们正在插入的 ID 值已经在表中存在。因此，它是重复的，该查询会生成以下错误：

In the above code block, the second insert statement returned an error saying "Duplicate entry '1' for key 'customers.ID" because the ID value we are inserting already exists in the table. Therefore, it is a duplicate and the query generates the following error −

ERROR 1062 (23000): Duplicate entry '1' for key 'customers.ID'

MySQL PRIMARY KEY Constraint

MySQL 中的 PRIMARY KEY 约束用于唯一标识表中的每条记录。这意味着，如果我们在表中的特定列上定义了主键，那么它必须包含 UNIQUE 值，并且不能包含 NULL 值。

The PRIMARY KEY constraint in MySQL is used to uniquely identify each record in a table. This means that, if we define primary key on a particular column in a table, it must contain UNIQUE values, and cannot contain NULL values.

Note: 对于一张表，我们只能有一个主键。

Note: We can have only a single primary key on a table.

Example

下面的查询在 CUSTOMERS 表的 ID 列上创建了一个 PRIMARY KEY：

The following query creates a PRIMARY KEY on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (ID)
);

一旦创建了表，向上面创建的表中插入以下记录：

Once the table is created, insert the following record into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (1, 'Nikhil', 18);
Query OK, 1 row affected (0.01 sec)

由于我们在 ID 列上添加了 PRIMARY KEY 约束，如果你尝试插入一个具有重复的 ID 值或 NULL 值的记录，它会生成一个错误。

Since we added the PRIMARY KEY constraint on the ID column, if you try to insert a record with duplicate ID value or NULL value, it will generate an error.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (1, 'Varun', 26);
ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (NULL, 'Datta', 19);
ERROR 1048 (23000): Column 'ID' cannot be null

正如我们在上面的查询中看到的，第一个插入语句成功插入到表中。而第二个和第三个语句返回错误，因为它们在主键列中包含重复值和 NULL 值，即 (ID)。

As we can see in the above queries, the first insert statement is successfully inserted into the table. Whereas the second and third statements returned an error because they contain a duplicate and a NULL value in the primary key column i.e. (ID).

MySQL FOREIGN KEY Constraint

MySQL 中的外键约束用于将一张表中的一个字段或字段集合链接到另一张表的某个主键。

The FOREIGN KEY constraint in MySQL is used to link a field or collection of fields in one table to the primary key of another table.

具有外键的表称为 child table ，而具有主键的表称为 parent table 或引用的表。

A table with the foreign key is called a child table and the table with the primary key is called the parent table or referenced table.

Example

以下查询在创建 ORDERS 表时在 CUST_ID 列上创建外键 −

The following query creates a FOREIGN KEY on the CUST_ID column when the ORDERS table is created −

Table: Customers

Table: Customers

CREATE TABLE CUSTOMERS (
   CUST_ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (CUST_ID)
);

Table: Orders

Table: Orders

CREATE TABLE ORDERS (
   ORDER_ID int NOT NULL,
   ORDER_NUMBER int NOT NULL,
   CUST_ID int,
   FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS (CUST_ID)
);

MySQL CHECK Constraint

MySQL 中的检查约束限制了可以插入列的值的范围。此约束确保列中插入的值必须满足提供的条件。

The CHECK constraint in MySQL restricts the range of values that can be inserted into a column. This constraint ensures that the inserted value in a column must be satisfied with the provided condition.

Example

以下查询在 CUSTOMERS 表的 AGE 列上创建检查约束，其中确保学生的年龄必须为 18 岁或以上 −

The following query creates a CHECK constraint on the AGE column of the CUSTOMERS table, where it ensures that the age of the student must be 18 or older −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   CHECK (AGE >= 18)
 );

创建表后，我们可以像下面所示将记录插入到上述创建的表中 −

Once the table is created, we can insert the records into the above created table as shown below −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, 'Nikhil', 18);

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(3, 'Datta', 19);

由于我们在 AGE 列上添加了检查约束，使学生的年龄应等于或大于 18 岁。如果您尝试插入年龄值小于 18 岁的记录，则会生成错误。

Since we added the CHECK constraint on the AGE column such that the age of the student should be equal or greater than 18. If you try to insert a record with age value less than 18, an error will be generated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(2, 'Varun', 16);
ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(4, 'Karthik', 15);
ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

Example

这里，以下查询在 multiple columns (AGE 和 ADDRESS) 上创建检查约束 −

Here, the following query creates a CHECK constraint on multiple columns (AGE and ADDRESS) −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40),
   CONSTRAINT CHECK_AGE CHECK (AGE >= 18 AND ADDRESS = "Mumbai")
);

现在，让我们向上面创建的表中插入如下记录：

Now, let us insert the following records into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(1, 'Nikhil', 18, 'Mumbai');
Query OK, 1 row affected (0.01 sec)

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(3, 'Datta', 19, 'Delhi');
ERROR 3819 (HY000): Check constraint 'CHECK_AGE_AND_ADDRESS' is violated.

第二个插入语句返回错误，因为它违反了检查约束的条件，即 (AGE >= 18 AND ADDRESS = "Mumbai")。

The second insert statement returned an error because it is violating the condition of the check constraint i.e. (AGE >= 18 AND ADDRESS = "Mumbai").

MySQL DEFAULT Constraint

MySQL 中的默认约束用于为表中的特定列分配默认值。当插入期间未提供其他值时，此默认值将应用于 DEFAULT 指定列中的任何新记录。

The DEFAULT constraint in MySQL is used to assign a default value to a specific column in a table. This default value gets applied to any new records in the DEFAULT specified column when no other value is provided during insertion.

Example

在以下查询中，我们在 CUSTOMERS 表的 ADDRESS 列上定义 DEFAULT 约束。当不插入值时，我们将 "Mumbai" 指定为默认值。 −

In the following query, we are defining the DEFAULT constraint on the ADDRESS column of the CUSTOMERS table. We assigned "Mumbai" as default value when no value is inserted. −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40) DEFAULT "Mumbai"
);

此处，我们在 ADDRESS 列中插入前两个记录时没有任何值。在第三个记录中，我们在插入 ADDRESS 值为 "Delhi"。

Here, we are inserting the first two records without any value in the ADDRESS column. In the third record, we are inserting the ADDRESS value as 'Delhi'.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, 'Nikhil', 18);

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(2, 'Varun', 16);

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(3, 'Datta', 19, 'Delhi');

执行以下查询以显示插入到上述创建的表中的记录 −

Exeucte the following query to display the records inserted in the above-created table −

Select * from CUSTOMERS;

在以下输出中，我们可以看到前两行的 ADDRESS 列中的值为默认的 "Mumbai"。

In the following output, we can see that the value in the ADDRESS column for the first two rows is by default "Mumbai".

MySQL CREATE INDEX Constraint

MySQL 中的 CREATE INDEX 约束用于在表中为一列或多列创建索引。

The CREATE INDEX constraint in MySQL is used to create indexes for one more columns in a table.

索引用于从数据库中获取数据，速度快得多。然而，用户无法看到正在操作的索引，相反，它们仅用于加速搜索和查询。

The indexes are used to fetch the data from the database much quicker. However, the users cannot see the indexes in action, instead, they are just used to speed up the searches and queries.

Example

这里，我们使用下面的查询创建名为 CUSTOMERS 的表 −

Here, we are creating a table named CUSTOMERS using the query below −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40),
   PRIMARY KEY (ID)
);

下面的查询在 CUSTOMERS 表的 ADDRESS 列上创建一个名为 "index_address" 的索引 −

The following query creates an index named "index_address" on the ADDRESS column of the CUSTOMERS table −

CREATE INDEX index_address ON CUSTOMERS (ADDRESS);

MySQL AUTO_INCREMENT Constraint

在表的特定列上定义 AUTO_INCREMENT 约束时，当一条新记录插入该列时，它将自动生成一个唯一编号。

When a AUTO_INCREMENT constraint is defined on a particular column of a table, it will automatically generate a unique number when a new record is inserted into that column.

默认情况下，起始值为 1，它将为每条新记录自动将自己的值增大 1。

By default, the starting value is 1, and it will automatically increment its value by 1 for each new record.

Example

下面的查询在 CUSTOMERS 表的 ID 列上添加一个 AUTO_INCREMENT 约束 −

The following query adds an AUTO_INCREMENT constraint on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (ID)
);

在下面的 insert 语句中，我们不插入 ID 值。

In the insert statements below, we are not inserting ID values.

INSERT INTO STUDENTS(NAME, AGE) VALUES('Nikhil', 18);
INSERT INTO STUDENTS(NAME, AGE) VALUES('Varun', 16);
INSERT INTO STUDENTS(NAME, AGE) VALUES('Datta', 19);

现在，执行下面的查询显示上面创建的表的记录 −

Now, execute the following query to display the records of the above-created table −

Select * from CUSTOMERS;

正如我们在下面的 STUDENTS 表中看到的那样，由于 ID 列上的 AUTO_INCREMENT 约束，ID 列中的值自动增加。

As we can see in the STUDENTS table below, the values in the ID column are automatically incremented because of the AUTO_INCREMENT constraint on the ID column.