Bidirectional foreign key constraint

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

Person (
  PersonID int primary key,
开发者_Python百科  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?

"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.

This is a perfect example of many-to-many relationship. To resolve that you should have intermediate PERSON_ADDRESS table. In other words;

PERSON table
person_id (PK)

ADDRESS table
address_id (PK)

PERSON_ADDRESS
person_id (FK) <= PERSON
address_id (FK) <= ADDRESS
is_primary (BOOLEAN - Y/N)

This way you can assign multiple addresses to a PERSON and also reuse ADDRESS records in multiple PERSONs (for family members, employees of the same company etc.). Using is_primary field in PERSON_ADDRESS table, you can identify if that person_addrees combination is a primary address for a person.

We mark the primary address in our address table and then have triggers that enforces only record per person can have it (but one record must have it). If you change the primary address, it will update the old primary address as well as the new one. If you delete a primary address and other addresses exist, it will promote one of them (basesd ona series of rules) to the primary address. If the address is inserted and is the first address inserted, it will mark that one automatically as the primary address.

The second FK (PersonId from Address to Person) is too restrictive, IMHO. Are you suggesting that one address can only have a single person?

From your design, it seems that an address can apply to only one person, so just use the PersonID as the key to the address table, and drop the AddressID key field.

I know I'll probably be crucified or whatever, but here goes...

I've done it like this for my "particular very own unique and non-standard" business need ( =( God I'm starting to sound like SQL DDL even when I speak).

Here's an exaxmple:

CREATE TABLE IF NOT EXISTS PERSON(
    ID INT, 
    CONSTRAINT PRIMARY KEY (ID), 
    ADDRESS_ID INT NOT NULL DEFAULT 1, 
    DESCRIPTION VARCHAR(255), 
    CONSTRAINT PERSON_UQ UNIQUE KEY (ADDRESS_ID, ...));

INSERT INTO PERSON(ID, DESCRIPTION) 
    VALUES (1, 'GOVERNMENT');

CREATE TABLE IF NOT EXISTS ADDRESS(
    ID INT, 
    CONSTRAINT PRIMARY KEY (ID), 
    PERSON_ID INT NOT NULL DEFAULT 1, 
    DESCRIPTION VARCHAR(255), 
    CONSTRAINT ADDRESS_UQ UNIQUE KEY (PERSON_ID, ...), 
    CONSTRAINT ADDRESS_PERSON_FK FOREIGN KEY (PERSON_ID) REFERENCES PERSON(ID));

INSERT INTO ADDRESS(ID, DESCRIPTION) 
    VALUES (1, 'ABANDONED HOUSE AT THIS ADDRESS');

ALTER TABLE PERSON ADD CONSTRAINT PERSON_ADDRESS_FK FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS(ID);

<...life goes on... whether you provide and address or not to the person and vice versa>

I defined one table, then the other table referencing the first and then altered the first to reflect the reference to the second (which didn't exist at the time of the first table's creation). It's not meant for a particular database; if I need it I just try it and if it works then I use it, if not then I try to avoid having that need in the design (I can't always control that, sometimes the design is handed to me as-is). if you have an address without a person then it belongs to the "government" person. If you have a "homeless person" then it gets the "abandoned house" address. I run a process to determine which houses have no users