mysql 级联更新,如何在MySQL中级联更新？

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.