List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:August 25 2008 9:37am
Subject:Re: Foreign Keys
View as plain text  
Hi Steffan, all !

Steffan A. Cline wrote:
> [[...]]
> I am hoping that by using FK based relationships I can just do one massive
> insert  into the parent table and include all related columns and somehow
> magically all field and relational keys fall into place.

AFAIK, this isn't possible.

Foreign keys (aka "referential integrity") has a different purpose:
Cross-table (or inter-table) consistency.

If one table refers to (the primary key of) another table, it should be 
guaranteed that this reference is an existing value (does point to an 
existing record).

If there were no target record, your data were inconsistent.
Foreign keys are meant to prevent such an inconsistency.

> Example:
> Parent table - People
> Columns - person_id, firstname, lastname
> Child table - Homes
> Columns - home_id, person_id, address

Using your example:
The purpose of referential integrity is to avoid the case where a 
"homes" record contains a "person_id" for which there is no "people" 
record, IOW the case of a home whose owner isn't known.

The relation is asymmetric, as indicated by "parent" and "child":
You cannot have a "child" record without a "parent",
but you can have a "parent" type record without an actual "child".

The typical example is "customer" and "order":
You cannot have an order without customer (so you must insert the 
customer first, and you must not delete a customer record from your data 
while there is an order associated with it),
but you can easily enter a (prospective) customer into your system who 
hasn't yet placed an order.

> Then I could do something like:
> insert into people (firstname, lastname, address) values ('xxx','xxx',xxx');
> And hopefully due to the FK relationship it would match the proper field and
> insert the data into the matching table and auto populate the person_id in
> the homes table with the corresponding parent row's PK (person_id)
> Am I totally off base or is something like this possible?

It isn't possible by using foreign keys, AFAIK.

You could do that by creating an "updateable join view": a view which 
creates a 1:1 relationship between "people" and "homes" on the 
"person_id" column.
1) AFAIK, MySQL doesn't yet support this, it is a ToDo item.
2) This would be a 1:1 relationship, for each "people_id" value there
    could be only *one* "homes" record (IOW, you couldn't define both a
    town and a summer house).

What foreign keys are good for:
Depending on how you define the options of the relationship, they would
- delete a "homes" record when you delete the owner's "people" record
   ("on delete cascade"), or
- prevent you from deleting a "people" record referencing a "homes"
   record ("on delete restrict").
I did not follow how far this is already implemented, depending on the 
MySQL version and the table handler.

Regards and HTH,

Joerg Bruehe,  MySQL Build Team,  joerg@stripped
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

Foreign KeysSteffan A. Cline23 Aug
  • Re: Foreign KeysJoerg Bruehe25 Aug
    • Re: Foreign KeysTom Nugent25 Aug
      • Re: Foreign KeysTom Nugent25 Aug