mysql 数据完整性约束

基础概念

MySQL 数据完整性约束是指在数据库设计中用于确保数据的准确性和一致性的规则。这些约束确保数据在插入、更新或删除时满足特定的条件，从而防止无效或不正确的数据进入数据库。数据完整性约束主要有以下几种类型：

实体完整性：确保表中的每一行数据都能被唯一标识，通常通过主键实现。
参照完整性：确保表之间的引用关系是有效的，通常通过外键实现。
域完整性：确保列中的数据满足特定的条件，如数据类型、长度、取值范围等。
用户定义完整性：允许用户自定义约束条件，以满足特定的业务需求。

类型

主键约束（PRIMARY KEY）：确保表中的每一行数据都能被唯一标识。
外键约束（FOREIGN KEY）：确保表之间的引用关系是有效的。
唯一约束（UNIQUE）：确保列中的数据是唯一的。
检查约束（CHECK）：确保列中的数据满足特定的条件。
非空约束（NOT NULL）：确保列中的数据不能为空。

应用场景

用户管理系统：在用户表中，可以使用主键约束确保每个用户的唯一性，使用非空约束确保必填字段不为空。
订单管理系统：在订单表中，可以使用外键约束确保订单与客户之间的引用关系是有效的。
库存管理系统：在库存表中，可以使用唯一约束确保每种商品的唯一性，使用检查约束确保库存数量不为负数。

常见问题及解决方法

问题1：外键约束失败

原因：通常是因为引用的主键不存在，或者引用的主键被删除。

解决方法：

确保引用的主键存在。
在删除主键之前，先删除或更新引用该主键的外键。

-- 示例：创建外键约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 示例：删除外键约束
ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;

问题2：唯一约束冲突

原因：通常是因为插入的数据与表中已有的数据重复。

解决方法：

确保插入的数据是唯一的。
在插入数据之前，先检查表中是否已存在相同的数据。

-- 示例：创建唯一约束
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 示例：插入数据时检查唯一性
INSERT INTO users (user_id, username, email)
SELECT 1, 'john_doe', 'john@example.com'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'john_doe' OR email = 'john@example.com');

问题3：检查约束失败

原因：通常是因为插入或更新的数据不满足检查约束的条件。

解决方法：

确保插入或更新的数据满足检查约束的条件。
修改检查约束的条件，使其更符合实际需求。

-- 示例：创建检查约束
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    stock_quantity INT CHECK (stock_quantity >= 0)
);

-- 示例：插入数据时满足检查约束
INSERT INTO products (product_id, product_name, stock_quantity)
VALUES (1, 'Product A', 10);