mysql数据完整性约束

基础概念

MySQL 数据完整性约束是指用于确保数据库表中的数据满足特定条件的一系列规则和限制。这些约束有助于维护数据的准确性和一致性，防止无效数据的插入或更新。

主要类型

主键约束（PRIMARY KEY）：
- 确保表中每行数据的唯一性。
- 一个表只能有一个主键。
- 主键列的值不能为 NULL。

唯一约束（UNIQUE）：
- 确保表中某一列或多列的值唯一。
- 可以有多个唯一约束。
- 唯一约束列的值可以为 NULL，但只能有一个 NULL 值。
外键约束（FOREIGN KEY）：
- 确保表中某一列的值必须是另一个表的主键值。
- 用于建立表与表之间的关系。
- 外键列的值可以为 NULL。
检查约束（CHECK）：
- 确保表中某一列的值满足特定的条件。
- MySQL 不直接支持 CHECK 约束，但可以通过触发器实现类似功能。
非空约束（NOT NULL）：
- 确保表中某一列的值不能为空。
- 非空列必须有默认值或在插入数据时提供值。

优势

数据一致性：通过约束确保数据的准确性和一致性。
数据完整性：防止无效数据的插入或更新。
简化应用逻辑：在数据库层面进行数据验证，减少应用层的逻辑处理。

应用场景

用户注册系统：使用唯一约束确保用户名的唯一性。
订单管理系统：使用外键约束确保订单与客户信息的关联。
库存管理系统：使用非空约束确保库存数量不为空。

常见问题及解决方法

1. 主键冲突

问题描述：插入数据时，主键值已存在。

解决方法：

-- 使用 INSERT IGNORE 忽略冲突
INSERT IGNORE INTO table_name (column1, column2) VALUES (value1, value2);

-- 使用 ON DUPLICATE KEY UPDATE 更新冲突记录
INSERT INTO table_name (column1, column2) VALUES (value1, value2)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2;

2. 外键约束失败

问题描述：插入或更新数据时，外键值不存在于引用表中。

解决方法：

-- 确保引用表中存在相应的主键值
INSERT INTO referenced_table (column1) VALUES (value1);

-- 插入或更新数据时，确保外键值存在
INSERT INTO table_name (column1, foreign_key_column) VALUES (value1, value2)
WHERE EXISTS (SELECT 1 FROM referenced_table WHERE primary_key_column = value2);

3. 唯一约束冲突

问题描述：插入数据时，唯一约束列的值已存在。

解决方法：

-- 使用 INSERT IGNORE 忽略冲突
INSERT IGNORE INTO table_name (unique_column, column2) VALUES (value1, value2);

-- 使用 ON DUPLICATE KEY UPDATE 更新冲突记录
INSERT INTO table_name (unique_column, column2) VALUES (value1, value2)
ON DUPLICATE KEY UPDATE column2 = value2;