mysql表级约束语句

基础概念

MySQL中的表级约束（Table-Level Constraints）是指对表中的数据施加的一系列规则和限制，以确保数据的完整性和一致性。这些约束可以在创建表时定义，也可以在表创建后添加。

类型

主键约束（PRIMARY KEY）：确保表中每一行都有一个唯一的标识符。
唯一约束（UNIQUE）：确保表中的某一列或多列的值是唯一的。
外键约束（FOREIGN KEY）：确保引用完整性，即表中的值必须匹配另一个表的主键值。
检查约束（CHECK）：确保列中的值满足特定的条件。
非空约束（NOT NULL）：确保列中的值不能为空。

应用场景

主键约束：通常用于标识表中的每一行，例如用户ID、订单ID等。
唯一约束：用于确保某些列的值是唯一的，例如电子邮件地址、用户名等。
外键约束：用于维护两个表之间的关系，例如订单表和用户表之间的关系。
检查约束：用于确保数据的合法性，例如年龄必须在0到120之间。
非空约束：用于确保某些列必须有值，例如用户名不能为空。

示例代码

-- 创建表时定义约束
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 0 AND age <= 120),
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES countries(country_id)
);

-- 表创建后添加约束
ALTER TABLE users
ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 120);

ALTER TABLE users
ADD CONSTRAINT fk_country FOREIGN KEY (country_id) REFERENCES countries(country_id);

遇到的问题及解决方法

问题1：主键冲突

原因：尝试插入重复的主键值。

解决方法：

-- 检查是否存在重复的主键值
SELECT user_id FROM users WHERE user_id = 1;

-- 如果存在重复值，可以选择删除重复记录或更新主键值
DELETE FROM users WHERE user_id = 1;

-- 或者更新主键值
UPDATE users SET user_id = 2 WHERE user_id = 1;

问题2：外键约束失败

原因：尝试插入的外键值在引用表中不存在。

解决方法：

-- 检查引用表中是否存在该外键值
SELECT country_id FROM countries WHERE country_id = 1;

-- 如果不存在，可以选择插入缺失的值或更新外键值
INSERT INTO countries (country_id, country_name) VALUES (1, 'China');

-- 或者更新外键值
UPDATE users SET country_id = 2 WHERE country_id = 1;

问题3：唯一约束冲突

原因：尝试插入重复的唯一值。

解决方法：

-- 检查是否存在重复的唯一值
SELECT username FROM users WHERE username = 'admin';

-- 如果存在重复值，可以选择删除重复记录或更新唯一值
DELETE FROM users WHERE username = 'admin';

-- 或者更新唯一值
UPDATE users SET username = 'admin2' WHERE username = 'admin';