Mysql – Same column present on two or more foreign key constraints

You will get errors if you restore a row that contains a value in nQuestionId2 that references a value in nQuestionId1 that has not been created yet.

There are a few ways to resolve this issue:

• You could ensure that you create the data in the correct order.
• You could disable the foreign keys before importing the data, then re-enable them after.
• If disable/re-enable is not an option, you could drop/recreate them.
• Alternatively you could split out the data so that the nQuestionId1 is in a separate table - this will allow you to restore all the nQuestionId1 values first.

I guess but the question really is about what you are trying to achieve.

I have seen designs like this that have a ID column and then a PARENT column that references the ID. In order to get the root parent you have to recurse through all of the parents of the object you are looking at. This means that even though a design like this may make sense logically, querying it could become quite difficult.

Database design is not just about mapping business rules and objects to tables and constraints, but also thinking about what is maintainable, how will the design hold up against large amounts of data, and being pragmatic about your approach.

I hope this helps you.

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;