如何在MySQL中级联更新？ [英] How to update on cascade in MySQL?

问题描述

让我们看看这个示例数据库：

我们可以看到，人取决于城市（person.city_id是外键）。我不删除行，我只是将它们设置为无效（活动= 0）。设置城市无效后，如何自动设置所有依赖此城市的人处于非活动状态？是否有比写入触发器更好的方法？

编辑：我只想将人的行设置为非活动状态，而不是将其设置为活动状态。

 <$> 
这里有一个解决方案使用级联外键来做你所描述的： c $ c> mysql>创建表城市（
 id int not null auto_increment，
 name varchar（45），
 active tinyint，
主键（id），
唯一键活性））; 
 
 mysql> create table person（
 id int not null auto_increment，
 city_id int，
 active tinyint，
主键（id），
外键（city_id，active）references城市（id，活动）更新级联）; 
 
 mysql>插入城市（名称，活动）值（'New York'，1）; 
 
 mysql> insert（person_id，active）values（1,1）; 
 
 mysql>选择* from person; 
 + ---- + --------- + -------- + 
 | id | city_id |活动| 
 + ---- + --------- + -------- + 
 | 1 | 1 | 1 | 
 + ---- + --------- + -------- + 
 
 mysql> update city set active = 0其中id = 1; 
 
 mysql>选择* from person; 
 + ---- + --------- + -------- + 
 | id |城市ID |活动| 
 + ---- + --------- + -------- + 
 | 1 | 1 | 0 | 
 + ---- + --------- + -------- + 
  

在MySQL 5.5.31上测试。

Let's look at this example database:

As we can see, person depends on the city (person.city_id is a foreign key). I don't delete rows, I just set them inactive (active=0). After setting city inactive, how can I automatically set all persons who are dependent on this city inactive? Is there a better way than writing triggers?

EDIT: I am interested only in setting person's rows inactive, not setting them active.

解决方案

Here's a solution that uses cascading foreign keys to do what you describe:

mysql> create table city (
  id int not null auto_increment, 
  name varchar(45), 
  active tinyint, 
  primary key (id),
  unique key (id, active));

mysql> create table person (
  id int not null auto_increment, 
  city_id int,
  active tinyint, 
  primary key (id), 
  foreign key (city_id, active) references city (id, active) on update cascade);

mysql> insert into city (name, active) values ('New York', 1);

mysql> insert into person (city_id, active) values (1, 1);

mysql> select * from person;
+----+---------+--------+
| id | city_id | active |
+----+---------+--------+
|  1 |       1 |      1 |
+----+---------+--------+

mysql> update city set active = 0 where id = 1;

mysql> select * from person;
+----+---------+--------+
| id | city_id | active |
+----+---------+--------+
|  1 |       1 |      0 |
+----+---------+--------+

Tested on MySQL 5.5.31.

这篇关于如何在MySQL中级联更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！