mysql - How to solve the problem that two tables refer to each other as foreign keys in a database table

Question

I have two tables, one is the employee table and the other is the department table.

• The employee table has two attributes: employee number and department number

• Department has two attributes: department number and manager number.

Obviously, the manager number should use the employee number in the employee table as a foreign key reference, and the department number in the employee table should use the department number in the department table as a foreign key reference.

As a result, there will be a conflict between the two tables being foreign key references to each other. How to solve it?

Answer 1

The table structure is unreasonable!

jue color table should be added

Add role fields to employee table

Character sheet

id	Character name
1	Manager
2	Female Secretary
3	Brick-moving dog

Employee list

id	Character	Department	Name
1	1	1	Manager
2	2	1	Xiaomi
3	3	1	Disi

Department table

id	Department name
1	Ministry of Industry
2	Business Department
3	R&D Department

Answer 2

Why is "mutual foreign key reference" a contradiction?

If you want to add a new department, and the manager of this department is also a new person, you can first use an old employee to "act" as the manager, and then modify it after the addition is successful. For example, adding a new financial department and manager Zhang San:

insert into 部门表 select id=财务部, manager=老王
insert into 职工表 select id=张三, department=财务部
update 部门表 set manager=张三 where id=财务部

The next question is, how to add when both tables are empty? At this time, you can add a row of data first and then add the foreign key constraints.