Joerg- Your comments below are excellent and my comments are just building
off what you have so clearly stated.
I think Stefan's best bet would be a stored procedure. Pass all the data,
insert the parent record (or make sure the parent record exists ... perhaps
it already is present) and then do an insert into the child table. The PK's
and FK's should be good.
Thanks and have a great day!
Tom
On Mon, Aug 25, 2008 at 2:37 AM, Joerg Bruehe <[EMAIL PROTECTED]> wrote:
> 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]
>
>
