Mysql--基础篇--约束（主键，外键，唯一，检查，枚举及复合约束等）

在MySQL中，约束（Constraints）是用于确保数据库中数据完整性和一致性的规则。通过定义约束，可以防止用户插入、更新或删除不符合特定条件的数据，从而保证数据的准确性和可靠性。MySQL支持多种类型的约束，每种约束都有其特定的作用和使用场景。

1、主键约束（Primary Key Constraint）

作用：

唯一性：确保表中的每一行都有一个唯一的标识符。
非空性：主键列不允许包含NULL值。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,  -- 主键，自动递增name VARCHAR(50) NOT NULL,age INT
);

解释：

id列是主键，确保每个员工都有一个唯一的标识符，并且该列不允许为NULL。
AUTO_INCREMENT：自动为每一行生成唯一的ID，通常用于主键列。

或者在创建表后添加主键：

ALTER TABLE table_name
ADD PRIMARY KEY (column1);

特点：

每个表只能有一个主键。
主键可以由单个列或多列组成（称为复合主键）。
主键列上的值必须是唯一的且不能为空。

2、外键约束（Foreign Key Constraint）

作用：

引用完整性：确保两个表之间的关系。外键列的值必须存在于另一个表的主键列中，或者为NULL。
级联操作：可以通过外键定义级联操作（如删除或更新时自动影响相关记录）。

示例：

CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT,order_date DATE,FOREIGN KEY (customer_id) REFERENCES customers(id)
);

解释：

orders表中的customer_id列是外键，引用customers表中的id列。
这意味着orders表中的customer_id必须是customers表中存在的id，或者为NULL。

或者在创建表后添加外键：

ALTER TABLE table_name
ADD FOREIGN KEY (column1) REFERENCES parent_table(parent_column);

级联操作：

ON DELETE CASCADE：当父表中的记录被删除时，子表中相关的记录也会自动删除。
ON UPDATE CASCADE：当父表中的主键值被更新时，子表中相关的外键值也会自动更新。
注意：
如果主表设置了外键，建表时没有指定级联操作。Mysql默认不允许删除或更新主表中的外键，会直接报出外键的错误提示。
级联示例：

CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT,order_date DATE,FOREIGN KEY (customer_id) REFERENCES customers(id)ON DELETE CASCADEON UPDATE CASCADE
);

注意事项：

外键列的数据类型必须与引用的主键列的数据类型兼容。
外键约束会增加一定的性能开销，尤其是在大规模数据操作时。

3、唯一约束（Unique Constraint）

作用：

唯一性：确保列中的所有值都是唯一的，但允许包含NULL值（多NULL值被视为不同的值）。

示例：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) UNIQUE, – 确保每个用户的电子邮件地址是唯一的，但可以为空
username VARCHAR(50)
);
解释：

email列上的唯一约束确保每个用户的电子邮件地址是唯一的。

或者在创建表后添加唯一约束：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1);

复合唯一约束：

可以为多个列组合定义唯一约束，确保这些列的组合值是唯一的。
示例：

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),UNIQUE (first_name, last_name)  -- 确保第一名称和姓氏的组合是唯一的
);

注意事项：

唯一约束允许NULL值，但多个NULL值被视为不同的值。
如果需要确保某一列既唯一又不允许NULL，可以同时使用NOT NULL和UNIQUE约束。

4、非空约束（Not Null Constraint）

作用：

强制非空：确保列中的值不能为空（即不允许NULL）。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,  -- 确保员工的名字不能为空age INT
);

解释：

name列上的NOT NULL约束确保每个员工的名字不能为空。

或者在创建表后修改列以添加非空约束：

ALTER TABLE table_name
MODIFY column1 datatype NOT NULL;

注意事项：

非空约束不能与AUTO_INCREMENT一起使用，因为AUTO_INCREMENT会自动为列生成值。
非空约束适用于所有数据类型，包括数值、字符串、日期等。

5、默认值约束（Default Constraint）

作用：

设置默认值：当插入新记录时，如果没有为某列提供值，则自动使用指定的默认值。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,age INT DEFAULT 18,  -- 如果没有提供年龄，默认为 18hire_date DATE DEFAULT CURRENT_DATE  -- 如果没有提供入职日期，默认为当前日期
);

解释：

age列上的默认值为18，如果插入新记录时未提供年龄，则自动使用18。
hire_date列上的默认值为CURRENT_DATE，表示当前日期。

或者在创建表后修改列以添加默认值：

ALTER TABLE table_name
ALTER COLUMN column1 SET DEFAULT default_value;

注意事项：

默认值可以是常量、表达式或函数（如CURRENT_DATE）。
默认值不会覆盖显式提供的值，只有在插入时未提供值时才会使用默认值。

6、检查约束（Check Constraint）

作用：

数据验证：确保列中的值满足特定的条件或范围。只有符合条件的值才能插入或更新到该列中。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,age INT CHECK (age >= 18 AND age <= 65),  -- 确保年龄在 18 到 65 之间salary DECIMAL(10, 2) CHECK (salary > 0)  -- 确保工资大于 0
);

解释：

age列上的检查约束确保年龄在18到65之间。
salary列上的检查约束确保工资大于0。

或者在创建表后添加检查约束：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

注意事项：

检查约束可以包含简单的条件（如 >=、<=、=）或更复杂的表达式。
检查约束只适用于单个列，不能跨多个列进行复杂检查。
在某些版本的MySQL中（如5.7及之前），检查约束虽然可以定义，但并不会实际执行。从MySQL 8.0.16开始，检查约束得到了完整的支持并会强制执行。

7、枚举约束（Enum Constraint）

作用：

限制取值范围：枚举类型允许你为列定义一组固定的、预定义的值。插入或更新时，列的值必须是这些预定义值之一。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,status ENUM('active', 'inactive', 'on_leave') NOT NULL  -- 状态只能是 'active'、'inactive' 或'on_leave'
);

解释：

status列上的枚举约束确保状态只能是active、inactive或on_leave。

注意事项：

枚举值是按定义顺序存储的，内部使用整数索引表示。例如，'active’对应索引1，‘inactive’ 对应索引2，依此类推。
枚举类型适合用于有限的、固定的选择集，如性别、状态等。

8、集合约束（Set Constraint）

作用：

多值选择：集合类型允许你为列定义一组固定的、预定义的值，并且该列可以存储多个值。每个值必须是这些预定义值之一。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,skills SET('Java', 'Python', 'C++', 'SQL') NOT NULL  -- 技能可以是多个值的组合
);

解释：

skills列上的集合约束允许存储多个技能，如’Java,Python’或’C++,SQL’。

注意事项：

集合类型适合用于多选一或多选多的情况，如技能、权限等。
集合值是按位存储的，内部使用位掩码表示。例如，'Java’对应位1，'Python’对应位2，依此类推。

9、复合约束

在MySQL中，可以为一个字段定义多个约束。

示例：

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,email VARCHAR(100) UNIQUE NOT NULL  -- 唯一 + 非空
);

解释： 唯一（Unique）+非空（NOT NULL）

唯一约束：确保该字段中的所有值都是唯一的，但允许NULL值（多个NULL值被视为不同的值）。
非空约束：确保该字段不允许NULL值。
所以这两个叠加在一起约束，就起到了唯一且不允许为null的约束。

在MySQL中，可以为一个字段定义多个约束.
常见的组合包括：

主键 + 非空
唯一 + 非空
外键 + 非空
默认值 + 非空
检查 + 非空
枚举 + 非空
集合 + 非空
唯一 + 默认值
外键 + 默认值
唯一 + 检查
外键 + 检查
通过合理组合多个约束，你可以更精确地控制字段的值，确保数据的完整性和一致性。然而，需要注意的是，某些约束组合可能会相互影响，因此在设计表结构时应仔细考虑每个约束的作用和限制。
所以通常不使用复合约束，除非必要场景。