MySQL数据库学习-基础约束

2025-08-06

介绍

约束：是作用表中字段上的规则，用于限制存储在表中的数据。 目的：保证数据库中数据的正确、有效性和完整性。 分类：

外键介绍：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

添加外键： CREATE TABLE 表名( 字段名 数据类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) ); ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KET(外键字段名) REFERENCES 主表(主表列名);

外键删除更新：

题目

任务零：数据库清理与数据初始化 (DDL/DML) 首先，为了确保我们从一个干净且可控的状态开始，请执行以下操作： 删除并重建 industrial_project_management 数据库： 这会清除之前所有的表和数据，让我们从零开始应用约束。 使用你之前学过的 DROP DATABASE 和 CREATE DATABASE 语句。 重新创建 projects, users, tasks 表： 这次创建时，请直接在 CREATE TABLE 语句中添加所有能添加的约束。

projects 表： project_id: 主键，自增长。 project_name: 不能为空，且名称必须唯一（不能有同名项目）。 description: 可以为空。 start_date: 不能为空。 end_date: 可以为空。 status: 不能为空，默认为 'Planning'，只能是 'Planning', 'Developing', 'Testing', 'Completed' 中的一个。 新增约束：确保 end_date 如果存在，必须晚于 start_date。

users 表： user_id: 主键，自增长。 username: 不能为空，唯一。 email: 可以为空，但如果填写则必须唯一。 role: 不能为空，默认为 'Developer'，只能是 'Developer', 'Tester', 'Product Manager', 'Product/Data Analyst' 中的一个。

tasks 表： task_id: 主键，自增长。 project_id: 不能为空，关联 projects 表的 project_id。 task_name: 不能为空。 task_details: 可以为空（这是你之前修改过的字段名）。 assigned_to_user_id: 可以为空，关联 users 表的 user_id。 due_date: 可以为空。 completion_date: 可以为空。 status: 不能为空，默认为 'Open'，只能是 'Open', 'In Progress', 'Done', 'Blocked', 'New' 中的一个。 外键约束： project_id：当父表 projects 中的项目被删除时，该项目下的所有任务也级联删除 (CASCADE)。 assigned_to_user_id：当父表 users 中的用户被删除时，任务的负责人ID应该被设置为空 (SET NULL)。 新增约束：如果 completion_date 存在，必须晚于 due_date。

插入初始数据： 插入至少3个用户（包含不同角色，例如 'hongcha' 作为 Product/Data Analyst，'lisi' 为 Developer，'zhaoliu' 为 Tester）。 插入至少2个项目（一个 'Smart Factory OS Development'，一个 'Data Analysis Platform'）。确保项目名称唯一。 插入至少5个任务，分布在不同项目，有些分配负责人，有些不分配，有些有 due_date，有些有 completion_date。 注意：在插入数据时，尝试故意插入一些违反你刚才定义的约束的数据（例如，重复的项目名、end_date 早于 start_date 的项目、completion_date 早于 due_date 的任务）。观察数据库如何响应这些非法操作。

任务一：约束的修改与验证 (DDL/DML) 现在我们有了应用了约束的表和一些初始数据。接下来，我们将通过修改约束和尝试操作来进一步理解它们。修改 users 表中的 email 约束： 目前 email 字段是 UNIQUE 且 NULLable。现在由于业务调整，公司决定不再强制邮箱唯一，但仍然允许其为空。 请移除 email 字段的 UNIQUE 约束。 思考：为什么不能简单地删除 UNIQUE 约束，而是可能需要先查找其名称？（不要求写代码，但思考这个点）。 为 tasks 表的 task_name 添加复合唯一约束： 现在发现，不同项目下可以有同名的任务（例如，Project A 可以有 Debug 任务，Project B 也可以有 Debug 任务），但同一个项目下不能有同名的任务。 请添加一个复合唯一约束，确保 (project_id, task_name) 组合是唯一的。

尝试违反约束的数据操作： 尝试插入一个 project_name 已经存在的项目，看看数据库是否报错。 尝试插入一个 end_date 早于 start_date 的项目，看看数据库是否报错。 尝试插入一个 completion_date 早于 due_date 的任务，看看数据库是否报错。 尝试插入一个 project_id 或 assigned_to_user_id 不存在的任务，看看数据库是否报错。 尝试删除一个 users 表中的用户，这个用户在 tasks 表中有被 assigned_to_user_id 关联的任务。观察 tasks 表中对应任务的 assigned_to_user_id 字段是否变为 NULL。 尝试删除一个 projects 表中的项目，看看 tasks 表中属于该项目的任务是否被级联删除。

任务二：约束的移除 (DDL) 有时候，业务需求变化会导致不再需要某个约束。 移除 projects 表的 end_date 晚于 start_date 的 CHECK 约束： 公司决定暂时放松对项目日期的严格校验，允许灵活设置。 请找到并移除 projects 表上确保 end_date 晚于 start_date 的 CHECK 约束。 提示：你可能需要先查询约束的名称。 移除 tasks 表的 (project_id, task_name) 复合唯一约束： 业务又变了，现在允许同一个项目下有多个同名任务，通过其他方式区分。 请移除这个复合唯一约束。

思考题（不要求写代码，但要求给出你的思路）： 你觉得在数据库设计时，主键约束、非空约束、唯一约束这三者之间有什么关联和区别？在选择使用哪个时，你的决策依据是什么？ CHECK 约束和 ENUM 类型都能限制字段的取值范围。在什么情况下你会优先选择 CHECK 约束，什么情况下会优先选择 ENUM 类型？它们各自的优缺点是什么？ 在实际项目开发中，你是更倾向于在数据库层面（如 CHECK 约束、外键）强制数据一致性和完整性，还是更多地依赖应用程序的代码逻辑来保证？为什么？

回答

drop database if exists industrial_project_management;
create database if not exists industrial_project_management;

use industrial_project_management;
create table projects(
project_id int primary key auto_increment,
project_name varchar(50) not null unique comment '项目名称',
description text null,
start_date date not null,
end_date date null,
constraint chk_dates check (end_date is null or end_date>=start_date),
status enum('Planning','Developing','Testing','Completed') 
not null default 'Planning'
)

create table users(
user_id int primary key auto_increment,
username varchar(50) not null unique,
email varchar(50) null unique,
role enum('Developer','Tester','Product Manager','Product/Data Analyst')
not null default 'Developer'
)

create table tasks(
task_id int primary key auto_increment,
project_id int not null,
FOREIGN KEY(project_id) REFERENCES projects(project_id) on delete cascade,
task_name varchar(50) not null,
task_details text null,
assigned_to_user_id int null,
FOREIGN KEY(assigned_to_user_id) REFERENCES users(user_id) on delete set null,
due_date date null,
completion_date date null,
constraint chk_taskdates check (completion_date is null or completion_date>=due_date),
status enum('Open', 'In Progress', 'Done', 'Blocked', 'New') 
not null default 'Open'
)

insert users (username,email,role) values
('hongcha','123@qq.com','Product/Data Analyst'),
('lisi','456@qq.com','Developer'),
('zhaoliu','789@qq.com','Tester');

insert projects (project_name,description,start_date,end_date,status) values
('Smart Factory OS Development',null,'2025-08-06','2025-08-10','Planning'),
('Data Analysis Platform','','2025-08-07',null,'Planning');

insert tasks (project_id,task_name,task_details,assigned_to_user_id,due_date,
completion_date,status) values
(3,'work1',null,1,'2025-04-10','2025-06-10','Done'),
(4,'work1',null,2,'2025-09-10',null,'Open'),
(3,'work2',null,3,'2025-10-10',null,'Blocked'),
(4,'work2',null,null,'2025-10-10',null,'Blocked'),
(3,'work3',null,null,'2025-11-06',null,'New');

alter table users modify email varchar(50) null;

ALTER TABLE tasks 
ADD CONSTRAINT uniq_project_task UNIQUE (project_id,task_name);

ALTER TABLE projects DROP CONSTRAINT chk_dates;
ALTER TABLE tasks DROP CONSTRAINT uniq_project_task;

/*
思考题（不要求写代码，但要求给出你的思路）：
你觉得在数据库设计时，主键约束、非空约束、唯一约束这三者之间有什么关联和区别？在选择使用哪个时，你的决策依据是什么？
回答：主键约束包含非空和唯一约束。每个表就一个主键约束，唯一约束适用于字段有非重复值的要求，非空约束适用于重要非空字段。

CHECK 约束和 ENUM 类型都能限制字段的取值范围。在什么情况下你会优先选择 CHECK 约束，什么情况下会优先选择 ENUM 类型？它们各自的优缺点是什么？
回答：ENUM是个数据类型，如果字段固定某些数据类型且利用索引，那么就采用ENUM。如果是业务临时性的或更加特殊涉及多表的取值范围，则采用CHECK。ENUM可以设置默认值，创建表时候更方便。CHECK更灵活。

在实际项目开发中，你是更倾向于在数据库层面（如 CHECK 约束、外键）强制数据一致性和完整性，还是更多地依赖应用程序的代码逻辑来保证？为什么？
回答：数据库层面和代码层面应该都需要有相应的。数据库显然更底层，更重要，设计到数据一致性和完整性的。而应用程序层面可以很好的规避一些错误问题并给予适当的提示，进一步辅助数据库。
*/
← 返回