如何更新MySQL中的级联？

Let's look at this example database: 如何更新MySQL中的级联？

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

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?

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

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

编辑：我只对将人的行设置为非活动而不是将其设置为活动感兴趣。

2 个解决方案

#1

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 5.5.31上测试过。

#2

Maybe you should reconsider how you define a person to be active.. Instead of having active defined twice, you should just keep it in the city table and have your SELECT statements return Person WHERE city.active = 1..

也许你应该重新考虑如何定义一个人是活跃的..你应该只将它保存在city表中并让你的SELECT语句返回Person WHERE city.active = 1 ..

But if you must.. you could do something like:

但如果你必须......你可以这样做：

UPDATE city C
LEFT JOIN person P ON C.id = P.city
SET C.active = 0 AND P.active = 0
WHERE C.id = @id

#1