【重学 MySQL】六十三、唯一约束的使用

用户11332765

发布于 2024-10-28 17:28:45

840

发布于 2024-10-28 17:28:45

文章被收录于专栏：编程

【重学 MySQL】六十三、唯一约束的使用

在 MySQL 中，唯一约束（UNIQUE Constraint）用于确保数据库表中的一列或多列的数据在整个表中是唯一的，即不允许有重复的值。唯一约束不仅可以在创建表时定义，也可以在表创建后通过修改表结构来添加。

创建表时定义唯一约束

在创建表时，可以通过 CREATE TABLE 语句中的 UNIQUE 关键字来定义唯一约束。

示例

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    password VARCHAR(100) NOT NULL
);

在这个示例中，username 和 email 列都被定义为唯一约束，这意味着在整个 users 表中，这两个字段的值必须是唯一的。

在已存在的表上添加唯一约束

如果表已经存在，可以使用 ALTER TABLE 语句来添加唯一约束。

示例

假设有一个已经存在的表 employees，我们想为 phone_number 列添加唯一约束：

ALTER TABLE employees
ADD CONSTRAINT unique_phone UNIQUE (phone_number);

或者，如果不使用命名约束（即匿名约束），可以简单地写为：

ALTER TABLE employees
ADD UNIQUE (phone_number);

删除唯一约束

要删除唯一约束，可以使用 ALTER TABLE 语句中的 DROP INDEX 子句（对于匿名约束）或 DROP CONSTRAINT 子句（对于命名约束）。

示例

假设我们有一个命名唯一约束 unique_phone，我们可以这样删除它：

ALTER TABLE employees
DROP CONSTRAINT unique_phone;

对于匿名唯一约束，我们需要知道约束的名称（MySQL 会在内部为匿名约束生成一个名称，通常是以 unique_ 开头，后跟列名和一些随机字符）。可以通过查询 information_schema 数据库中的 TABLE_CONSTRAINTS 表来找到这个名称。

SELECT CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'employees'
  AND CONSTRAINT_TYPE = 'UNIQUE';

找到约束名称后，可以使用 DROP INDEX 来删除它：

ALTER TABLE employees
DROP INDEX unique_phone_xxxx;  -- 替换为实际的约束名称

复合唯一约束

复合唯一约束是指在数据库中，对两个或多个列的组合设置唯一性约束，以确保这些列的组合在表中是唯一的。

案例背景

假设有一个名为students的学生表，用于存储学生的学号、姓名、班级编号和座位号。为了确保同一个班级中每个学生的座位号是唯一的，需要对班级编号和座位号这两个列设置复合唯一约束。

创建表并添加复合唯一约束

可以使用以下SQL语句来创建students表，并添加复合唯一约束：

CREATE TABLE students (
    student_id INT PRIMARY KEY, -- 学号，主键
    name VARCHAR(50), -- 姓名
    class_id INT, -- 班级编号
    seat_number INT, -- 座位号
    UNIQUE KEY unique_class_seat (class_id, seat_number) -- 复合唯一约束
);

在上述SQL语句中，unique_class_seat是复合唯一约束的名称，它确保了class_id和seat_number这两个列的组合在表中是唯一的。

插入数据测试

现在，可以尝试插入一些数据来测试复合唯一约束的效果：

-- 插入数据成功
INSERT INTO students (student_id, name, class_id, seat_number) VALUES (1, '张三', 101, 1);
INSERT INTO students (student_id, name, class_id, seat_number) VALUES (2, '李四', 101, 2);
INSERT INTO students (student_id, name, class_id, seat_number) VALUES (3, '王五', 102, 1);

-- 插入重复数据失败，因为班级编号101和座位号1的组合已经存在
INSERT INTO students (student_id, name, class_id, seat_number) VALUES (4, '赵六', 101, 1);

在上述插入操作中，前三个INSERT语句都成功执行，因为插入的数据没有违反复合唯一约束。而最后一个INSERT语句则失败，因为尝试插入的班级编号101和座位号1的组合已经存在于表中，违反了复合唯一约束。