sql server - Bidirectional foreign key constraint

Question

I'm thinking of designing a database schema similar to the following:

Person (
  PersonID int primary key,
  PrimaryAddressID int not null,
  ...
)

Address (
  AddressID int primary key,
  PersonID int not null,
  ...
)

Person.PrimaryAddressID and Address.PersonID would be foreign keys to the corresponding tables.

The obvious problem is that it's impossible to insert anything into either table. Is there any way to design a working schema that enforces every Person having a primary address?

Answer 1

"I believe this is impossible. You cannot create an Address Record until you know the ID of the person and you cannot insert the person record until you know an AddressId for the PrimaryAddressId field."

On the face of it, that claim seems SO appealing. However, it is quite propostrous.

This is a very common kind of problem that the SQL DBMS vendors have been trying to attack for perhaps decades already.

The key is that all constraint checking must be "deferred" until both inserts are done. That can be achieved under different forms. Database transactions may offer the possibility to do something like "SET deferred constraint checking ON", and you're done (were it not for the fact that in this particular example, you'd likely have to mess very hard with your design in order to be able to just DEFINE the two FK constraints, because one of them simply ISN'T a 'true' FK in the SQL sense !).

Trigger-based solutions as described here achieve essentially the same effect, but those are exposed to all the maintenance problems that exist with application-enforced integrity.

In their work, Chris Date & Hugh Darwen describe what is imo the true solution to the problem : multiple assignment. That is, essentially, the possibility to compose several distinct update statements and have the DBMS act upon it as if that were one single statement. Implementations of that concept do exist, but you won't find any that talks SQL.