MySQL----约束

一.前言

MySQL的约束是用于限制数据库表中数据的完整性和一致性的规则。它们定义了表中列的取值范围、关系和其他限制条件。

二.约束类型

MySQL支持以下几种约束类型：

1. 主键约束（Primary Key Constraint）：主键是用于唯一标识表中每一行数据的列或列组合。它保证了表中的每一行都具有唯一的标识，不能重复并且不允许为空值。
   
2. 唯一约束（Unique Constraint）：唯一约束用于确保表中的某一列或列组合的值是唯一的，但允许为空值。一个表可以有多个唯一约束。
   
3. 非空约束（Not Null Constraint）：非空约束用于确保表中的某一列不允许为空值。如果对应列的值为空，插入或更新操作将被拒绝。
   
4. 外键约束（Foreign Key Constraint）：外键约束用于建立表与表之间的关系。它定义了一个列或列组合，该列的值必须在另一个表的主键或唯一约束中存在。外键约束可以确保数据的一致性和完整性。
   
5. 检查约束（Check Constraint）：检查约束用于定义表中某一列的取值范围或条件。它可以限制列的取值必须满足指定的条件，例如大于某个值、小于某个值或满足某个表达式。
   
6. 默认值约束（Default Constraint）：MySQL 默认值约束用来指定某列的默认值。
   例如某公司女性员工较多，性别就可以默认为“女”。如果插入新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。
   

以上 6 种约束中，一张表最多只能有一个主键，但是可以是复合主键一个。
数据表中只能有一个主键约束，其它约束可以有多个。

使用：

这些约束可以在创建表时定义，也可以在已存在的表上添加或删除。

作用：

它们可以保证数据的完整性，避免不符合业务规则的数据被插入或更新到数据库中。

使用desc 表名，可以看到约束的情况

三.约束使用的语法

3.1 主键约束(PRIMARY KEY)

3.1.1 创建表时设置主键约束

1）设置单字段主键

【1】在定义字段的同时指定主键，语法格式如下：

<字段名> <数据类型> PRIMARY KEY [默认值]

例如：

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

【2】定义完所有字段之后指定主键，语法格式如下：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
例如：将id列设置为主键 如下：

mysql> CREATE TABLE table_name
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );

mysql> DESC table_name1;

2）设置联合主键

联合主键：这个表的主键是由其中多个字段组成的

语法格式如下：
PRIMARY KEY [字段1，字段2，…,字段n]

注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束

mysql> CREATE TABLE table_name
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> salary FLOAT,
    -> PRIMARY KEY(id,name)
    -> );

mysql> DESC table_name1;

3.1.2 修改表时添加主键约束

前提：要确保设置成主键约束的字段中值不能够有重复的，并且要保证是非空的

语法格式如下：

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

3.1.3 删除主键约束

删除主键约束的语法格式如下所示：

ALTER TABLE <数据表名> DROP PRIMARY KEY;

由于主键约束在一个表中只能有一个，因此不需要指定主键名就可以删除一个表中的主键约束。

3.2 唯一约束(UNIQUE)

3.2.1 创建表时设置唯一约束

语法 : <字段名> <数据类型> UNIQUE

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

3.2.2 修改表时设置唯一约束

语法格式为：

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

3.2.3 删除唯一约束

语法格式如下：

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

3.2.4 示例

设置唯一约束后查看:

mysql> DESC table_name2;

3.3 非空约束(NOT NULL)

3.3.1 在创建表时设置非空约束

使用 NOT NULL 关键字设置非空约束，具体的语法格式如下：

<字段名> <数据类型> NOT NULL;

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype,
    ...
);

3.3.2 修改表时设置非空约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

3.3.3 删除非空约束

语法：

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

3.3.4 示例：

原结构：

mysql> DESC table_name3;

设置salary非空约束后：

mysql> DESC table_name3;

3.4 外键约束¹(FOREIGN KEY)

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性

3.4.1 在创建表时设置外键约束

在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]

REFERENCES <主表名> 主键列1 [，主键列2，…]

语法

CREATE TABLE table_name1 (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES table_name2(column_name)
);

3.4.2 在修改表时添加外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

在修改数据表时添加外键约束的语法格式如下：

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>

FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

3.4.3 删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

删除外键约束的语法格式如下所示：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

3.4.4 示例：

创建表tb_dept1

mysql> CREATE TABLE tb_dept1

-> (

-> id INT(11) PRIMARY KEY,

-> name VARCHAR(22) NOT NULL,

-> location VARCHAR(50)

-> );

创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id

mysql> CREATE TABLE tb_emp6

-> (

-> id INT(11) PRIMARY KEY,

-> name VARCHAR(25),

-> deptId INT(11),

-> salary FLOAT,

-> CONSTRAINT fk_emp_dept1

-> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)

-> );

查询结果：

mysql> DESC tb_emp6;

注意：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。例如，两者都是 INT 类型，或者都是 CHAR 类型。如果不满足这样的要求，在创建从表时，就会出现“ERROR 1005(HY000): Can't create table”错误。

3.5 检查约束(CHECK)

3.5.1在创建表时设置检查约束

检查约束使用 CHECK 关键字，具体的语法格式如下：

CHECK <表达式>

“表达式”指的就是 SQL 表达式，用于指定需要检查的限定条件。

语法

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CHECK (condition)
);

注意：若将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的 CHECK 约束。该约束可以同时对表中多个列设置限定条件。

3.5.2 在修改表时添加检查约束

如果一个表创建完成，可以通过修改表的方式为表添加检查约束。

修改表时设置检查约束的语法格式如下：

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

3.5.3 删除检查约束

修改表时删除检查约束的语法格式如下：

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

3.5.4 添加检查约束示例：

在 test_db 数据库中创建 tb_emp7 数据表，要求 salary 字段值大于 0 且小于 10000，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_emp7
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CHECK(salary>0 AND salary<100),
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected (0.37 sec)

3.6 默认值约束(DEFAULT)

3.6.1创建表时设置默认值约束

创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法格式如下：

<字段名> <数据类型> DEFAULT <默认值>;

其中，“默认值”为该字段设置的默认值，如果是字符类型的，要用单引号括起来。

语法

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype,
    ...
);

3.6.2 在修改表时添加默认值约束

修改表时添加默认值约束的语法格式如下：

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

3.6.3 修改表时删除默认值约束

语法格式如下：

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

3.6.4 增加默认约束示例：

修改前：

mysql> DESC table_name5;

将name默认为LiMing:

mysql> DESC table_name5;

四.查看约束信息命令

1. 查看表的所有约束：
   SHOW CREATE TABLE table_name; //这将显示表的创建语句，其中包含了所有的约束信息。
   
2. 查看表的主键约束：
   SHOW KEYS FROM table_name WHERE Key_name = ‘PRIMARY’;
   

//这将显示表的主键约束信息，包括主键字段和排序方式。

3. 查看表的外键约束：
   SELECT
   CONSTRAINT_NAME,
   COLUMN_NAME,
   REFERENCED_TABLE_NAME,
   REFERENCED_COLUMN_NAME
   FROM
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE
   TABLE_NAME = ‘table_name’
   AND REFERENCED_TABLE_NAME IS NOT NULL;
   //这将显示表的外键约束信息，包括约束名称、字段名称、引用的表名和引用的字段名。
   
4. 查看表的唯一约束：
   SELECT
   CONSTRAINT_NAME,
   COLUMN_NAME
   FROM
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE
   TABLE_NAME = ‘table_name’
   AND CONSTRAINT_NAME <> ‘PRIMARY’
   AND CONSTRAINT_NAME <> ‘FOREIGN’;
   

//这将显示表的唯一约束信息，包括约束名称和字段名称。

请注意，上述命令中的"table_name"需要替换为实际的表名。

1. 定义外键时，需要遵守下列规则：
   1.主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
   2.必须为主表定义主键。
   3.主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
   4.在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
   5.外键中列的数目必须和主表的主键中列的数目相同。
   6.外键中列的数据类型必须和主表主键中对应列的数据类型相同。 ↩︎