Mysql 简明教程

MySQL − Constraints

MySQL Constraints

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

MySQL 约束有两种类型。

  1. Column level constraints: 这些类型的约束只适用于表中的一列。

  2. Table level constraints: 这些约束适用于整个表。

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

Syntax

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

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 约束。

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

Example

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

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

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

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

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

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

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

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

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

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

这会生成以下错误：

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

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

MySQL UNIQUE Constraint

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

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

Example

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

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

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

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 值已经在表中存在。因此，它是重复的，该查询会生成以下错误：

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

MySQL PRIMARY KEY Constraint

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

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

Example

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

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

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

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

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

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

MySQL FOREIGN KEY Constraint

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

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

Example

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

Table: Customers

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

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 中的检查约束限制了可以插入列的值的范围。此约束确保列中插入的值必须满足提供的条件。

Example

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

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

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

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

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

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

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) 上创建检查约束 −

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

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

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")。

MySQL DEFAULT Constraint

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

Example

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

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

此处，我们在 ADDRESS 列中插入前两个记录时没有任何值。在第三个记录中，我们在插入 ADDRESS 值为 "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');

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

Select * from CUSTOMERS;

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

MySQL CREATE INDEX Constraint

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

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

Example

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

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

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

CREATE INDEX index_address ON CUSTOMERS (ADDRESS);

MySQL AUTO_INCREMENT Constraint

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

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

Example

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

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

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

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

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

Select * from CUSTOMERS;

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