mysql 逻辑外键的设置

本文介绍如何使用SQL外键约束确保数据一致性,包括不同场景下的外键设置与操作演示。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

本博客转载自     https://www.jianshu.com/p/ab58fa7e8146

本博客字体标红部分为设置外键的SQL语句

1.假如我们现在有个这样的需求,我们公司有以下几个部门,“研发部”,“产品部”,“运维部”,当有新员工进入的时候,需要只能加入以下三个部门的某个部门,没有列表所示的部门将不能加入,这个时候,我们可以用到外键

我们来创建两个表,create table if NOT EXISTS dept(id TINYINT AUTO_INCREMENT PRIMARY KEY,name varchar(20));这是个部门表,

create table if NOT EXISTS persion(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

userName varchar(20) NOT null UNIQUE ,

depid TINYINT

);这是员工表,接下来我们添加外键,    ALTER TABLE PERSION ADD CONSTRAINT PER_FK_DEPT FOREIGN KEY(depid) REFERENCES DEPT(id)    --添加外键  PER_FK_DEPT;PER_FK_DEPT使我们的外键名字,这个时候我们的外键就添加成功了,在我们没有指定的情况下就是默认的NO ACTION    情况,下面,我们来试一下,首先往部门表里面插入几个部门    

INSERT INTO dept(NAME) VALUES("研发部");

INSERT INTO dept(NAME) VALUES("产品部");

INSERT INTO dept(NAME) VALUES("运维部");

OK ,三个部门添加成功了,SELECT *FROM DEPT;看一下数据

 

再往第二个表里面插入三条数据

INSERT INTO persion(userName,depid) VALUES('孙1',1);

INSERT INTO persion(userName,depid) VALUES('王2',2);

mysql> INSERT INTO persion(userName,depid) VALUES('张三',3);

OK,这个时候,我们插入了三条数据,他们的depid分别为1,2,3,也就是对应的dept表里面的三个部门,这个时候,我们再插入一条数据

    INSERT INTO persion(userName,depid) VALUES('赵4',4);

这个时候会看到控制台报错,

 

原因是员工表里面的deptid和部门表里面的id是关联的,他的外键和部门必须对应,这个时候插入一个员工到不存在的部门是不可以的,这个时候我们删除一个部门看一下,

 

又报错了,这是因为外键的原因,如果想删除某个部门,必须删除这个部门下的员工才可以将部门删除,接下来,我们先从员工表里面把某个员工删除,再来删除某个部门试一下, DELETE FROM persion where depid =1;

DELETE FROM dept where id =1;

 

OK,这个时候,就可以看到部门删除成功了,对应的员工也删除了,那么,我们不想删除员工,删除部门的时候,直接将对应的员工删除怎么做呢,就是下面说的这种情况

2.假如这个时候我们想删除某个部门,取消某个部门,比如我们想把运维部去掉,和到研发部门里面,而这个时候有两种情况,一是,加入删除了这个部门,而这个部门下所有的人都应该删除,第二种情况就是,删除了这个部门,而这个部门下的员工不删除,只是无家可归,我们可以给个null,这个时候,我们也可以用外键

如果我们想删除部门的时候,将员工也删除掉怎么做呢

首先先将我们刚才创建的外键删除了,重新来设置一下外键

ALTER TABLE persion DROP FOREIGN KEY PER_FK_DEPT;     --  删除外键PER_FK_DEPT

 

,OK,我们看一下是否删除成功,看一下表结构

SHOW CREATE TABLE PERSION \G;

 

可以看到,虽然外键删除成功了,三十索引还在,接下来把索引也删除了,

    ALTER TABLE persion DROP INDEX PER_FK_DEPT;       --删除索引 PER_FK_DEPT

然后看一下表结构,

DESC persion;

 

可以发现,索引删除了,

接下来我们创建一个外键

ALTER TABLE persion ADD CONSTRAINT PER_FK_DEPT FOREIGN KEY(depid) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;   --创建外键,使主表删除信息的同时,从表相关联的也删除,主表修改,从表关联的外键也修改

这个时候,我们就创建成了一个级联的外键,这个时候,当我们删除部门表里面的某个部门的时候,员工表里面,这个部门对应的所有员工都会跟着删除,当我们更新部门表里的部门的时候,员工表也会跟着删除,试一下,先看一下,两个表里面的数据

 

可以看到数据是一一对应的,这个时候我想把所有的部门编号都加10,而这个时候,我们的员工表里面的员工对用的部门ID也会跟着加10,那么会不会做到呢

update dept set id=id+10;

这个时候,我们再来看一下两个标的数据,

 

可以看到,当我们更改部门表的时候,员工表也跟着删除,这个时候,我想删除产品部这个部门,那么员工表里面的员工会跟着删除吗?

DELETE FROM dept where id =12;

再看一下表里的数据

 

可以看到,两个表里面的数据都删除了。

3.上面那种情况是删除主表,字表也跟着删除,现在我想删除某个部门的时候,里面的人不删除,只是让他无家可归,给个null,怎么做呢

首先删除我们刚才创建的外键

ALTER table persion DROP FOREIGN KEY PER_FK_DEPT;    --删除外键  PER_FK_DEPT

ALTER table persion DROP INDEX PER_FK_DEPT;  --删除索引   PER_FK_DEPT

然后我们再创建一个外键

ALTER table persion ADD CONSTRAINT PER_FK_DEPT FOREIGN KEY(depid) REFERENCES dept(id) on delete SET NULL on update SET NULL;  --创建外键,使主表中的信息删除或者更新,从表中外键的字段值变为null。

OK,这时候,我们再更新或者删除主表中的数据试一下,

DELETE FROM dept where id = 13;

OK ,我们删除成功了,这个时候,我们再来看一下两个标的数据

 

这个时候,可以看到,主表的数据已经完全删光了,而从表对应的人,只是给了个null,人还在,只是没有部门了,达到了我们的要求

到这里,我们的物理外键讲完了,一般开发中,我们很少用到物理外键,不让数据库去部或错误,我们认为去捕获,所以一般我们用到逻辑外键。

 

<think>嗯,用户想知道在MySQL中如何创建和设置约束。我需要仔细回想一下相关知识,确保回答正确。首先,约束用于关联两个表,确保数据完整性。用户可能已经了解基础,但需要具体步骤。 记得设置的几个关条件:存储引擎必须是InnoDB,列和主表列的数据类型要一致,主表的列必须是主或唯一索引。这些要点得在回答中提到,避免用户遇到问题。 创建有两种常见情况,建表时设置和后期修改表添加。我应该分别举例说明。比如建表时用FOREIGN KEY关字,后期用ALTER TABLE加上ADD CONSTRAINT。需要给出具体的SQL语句示例,像CREATE TABLE和ALTER TABLE的用法。 还要注意的名称,比如FK_开头,这样更规范。同时,约束规则比如ON UPDATE和ON DELETE的处理方式,比如CASCADE、SET NULL这些选项,用户可能也需要了解,但问题可能主要关注创建步骤,所以可以简要提到。 参考用户提供的引用,引用[1]提到使用ALTER TABLE和FOREIGN KEY,引用[2]有建表时的例子。需要将这些信息整合到回答中,确保引用正确。 可能用户遇到的常见错误,比如引擎不是InnoDB,或者数据类型不匹配,这些也应该作为注意事项提醒用户。此,权限问题也可能导致无法创建,但可能属于进阶内容,是否需要包含呢?根据用户问题,可能基础为主,先提主要点。 最后,生成相关问题时要覆盖的作用、修改删除方法、错误处理和应用场景,这样用户能进一步学习。检查是否有遗漏,比如语法是否正确,是否需要使用反引号包裹表名和列名,还是示例中已经正确处理。确保示例代码准确,比如分号的位置,约束名称的指定是否正确。 总结步骤:先说明的作用,然后详细步骤分建表时和后期添加,给出代码示例,注意事项,最后相关问题。这样结构清晰,用户能逐步操作。</think>在 MySQL设置约束的步骤如下,通过关联主表和从表来保证数据完整性: --- ### **一、约束的作用** 约束用于确保从表(子表)中某个字段的值必须与主表(父表)中对应字段的值匹配,防止无效数据的插入或更新[^1][^2]。 --- ### **二、设置的条件** 1. 存储引擎必须为 **InnoDB** 2. 字段与主表字段的**数据类型必须一致** 3. 主表字段需是**主**或**唯一索引** --- ### **三、具体操作步骤** #### **1. 建表时直接定义** 通过 `FOREIGN KEY` 关字指定,语法如下: ```sql CREATE TABLE 从表名 ( 列名 数据类型, ... CONSTRAINT 名 FOREIGN KEY (列名) REFERENCES 主表名 (主表列名) [ON DELETE 规则] [ON UPDATE 规则] ); ``` **示例**:创建 `tb_emp6` 表并关联 `tb_dept1` 表的 `id` 字段 ```sql CREATE TABLE tb_emp6 ( id INT PRIMARY KEY, name VARCHAR(50), deptId INT, CONSTRAINT FK_deptId FOREIGN KEY (deptId) REFERENCES tb_dept1 (id) ON DELETE CASCADE ON UPDATE CASCADE ); ``` #### **2. 修改现有表添加** 使用 `ALTER TABLE` 语句动态添加: ```sql ALTER TABLE 从表名 ADD CONSTRAINT 名 FOREIGN KEY (列名) REFERENCES 主表名 (主表列名); ``` **示例**:为 `orders` 表的 `user_id` 添加约束,关联 `users` 表的 `id`[^1] ```sql ALTER TABLE orders ADD CONSTRAINT FK_user_id FOREIGN KEY (user_id) REFERENCES users (id); ``` --- ### **四、注意事项** 1. 若主表数据被从表引用,**禁止直接删除主表记录**(除非使用 `ON DELETE CASCADE`) 2. 字段**允许为 NULL**,但需根据业务逻辑决定是否设置非空约束 3. 使用 `SHOW CREATE TABLE 表名` 可查看定义 --- ### **五、约束规则** | 规则类型 | 说明 | |----------------|------------------------------| | `CASCADE` | 主表更新/删除时,从表同步操作 | | `SET NULL` | 主表操作后,从表字段设为 NULL | | `RESTRICT` | 禁止主表操作(默认行为) | | `NO ACTION` | 与 `RESTRICT` 等效 | ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值