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.
Drawbacks:
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,
Jörg
--
Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
