How to solve circular reference between two tables?

Question

Pretty easy example:

A Company can have N Locations, and one of these Locations is the Main Location. Can we solve this problem with MySQL foreign keys?

Our current approach is to have a pointer to Company in each Location (company_id) and a pointer to Location in Company (main_location_id).

Assuming we don't allow null references, this approach is obviously wrong because it forbids creation: You cannot create a company because it has no main_location_id and you cannot create a Location because if has no company_id. Transactions do not help given that validations are still run in each step of the transaction.

Any ideas?

Thanks!

edit 1

Some people proposed to use a flag with a unique index in Location or in an intermediate table in order to solve the issue. We have already considered it but it has one main issue: It adds overhead since adding a new main location would require traversing all other locations to set their flags to false.

edit 2

Our preferred solution (in an ideal world) would be to check validations at the end of a transaction, and not in every step. This can be done by disabling foreign key checks, as it has been proposed in the comments, but this is of course a suboptimal solution.

Conclusion:

There is no way to do it with MySQL without relaxing one of the constraints. We'll use a before insert check in company to check location reference is not null.

Nevertheless, I'm still intrigued why MySQL does not allow to pass validations at the end of the transaction, instead of doing it in every step.

Answer 1

You must have three tables.

The first, your Company table where you have scalar informations about your company (as name, fiscal code and so on)

The second, a table with Location without link to your company. In this table, i.e., you have rows as:

• Trafalgar Square
• Umberto Street

and so on

The third, a middle table, CompanyLocation, where you have a double foreign key, companyId and locationId, and a third column represented by a flag (isPrimary)

That's all