Mysql – Update all foreign keys to a different primary key

foreign keyMySQL

In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. Let's say the same delivery service is represented by primary key 3 and 7.

Businesses:
| id | name           |
| 3  | Planet Express |
| 7  | Planet Express |

Lots of other tables foreign key to this business table. (Employees, etc)

Employees:
| id  | name   | business |
| 999 | Fry    | 3        |
| 666 | Bender | 7        |

Fry and Bender really do work for the same business. I'd like to delete the business row with primary key 7, and tell all the foreign key tables that instead of cascade delete, or cascade null, they should update to point to primary key 3.

Obviously I could do this by hand, but I'd rather make the database do it on my behalf since it has a better memory for this kind of thing. Is there a way to do this? Like DELETE FROM business WHERE id = 7 ON CASCADE set 3; or something?

Best Answer

Probably not interesting for the OP but I'll give it a shot anyhow. Given the current tables:

create table Businesses 
( id int not null primary key
, name varchar(20) not null) engine=innodb;

create table Employees 
( id int not null primary key
, name varchar(20) not null
, business int not null) engine=innodb;

insert into Businesses (id, name) 
values (3,'Planet Express'),(7,'Planet Express'),(11,'Whatever');

insert into Employees (id, name, business) 
values (999,'Fry',3),(666,'Bender',7),(333,'Bob',11);

To fix the immediate problem we can update a join against a derived table like:

update Employees e 
join (
    select min(id) as min_id, max(id) as max_id, name 
    from Businesses 
    group by name having count(1) = 2
) as b on e.business = b.max_id 
    set e.business = b.min_id;

select * from Employees;
+-----+--------+----------+
| id  | name   | business |
+-----+--------+----------+
| 333 | Bob    |       11 |
| 666 | Bender |        3 |
| 999 | Fry    |        3 |
+-----+--------+----------+

and then delete the duplicate business. Note that I assume that there are exactly two rows involved in each duplicate.

For the long run I would suggest several things. Names like id, name, etc are to vague, name them something like business_id and use that name throughout the model.

If the name of a Businesses is supposed to be unique, add a constraint that ensures this. Something like:

create table Businesses 
( business_id int not null primary key
, business_name varchar(20) not null unique
) engine=innodb;

create table Employees 
( employee_id int not null primary key
, employee_name varchar(20) not null
, business_id int not null
,    constraint fk_business foreign key (business_id)
                            references Businesses (business_id) 
) engine=innodb;
Related Question