3

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?

  • Declare the FK as ON UPDATE CASCADE? Does your RDBMS support changing the declarations? – Vérace Jun 11 '15 at 20:52
  • @Vérace How will this solve the issue? – ypercubeᵀᴹ Jun 11 '15 at 21:07
  • Misread, apologies to the OP. – Vérace Jun 11 '15 at 21:45
  • @Vérace No problem. Actually, after rethinking, your suggestion can be of help (but not on its own) in some complex scenario (example: another table that references Employees via a composite foreign key that includes businessID). – ypercubeᵀᴹ Jun 12 '15 at 0:21
  • @Jeremy which DBMS is this for? (Oracle, DB2, SQL-Server, MySQL, Postgres, ...)? – ypercubeᵀᴹ Jun 12 '15 at 0:23
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.