I forgot to mention that the only benefit of a stored procedure would be
minimizing code in your application. you'd have a single call to the stored
procedure... though some people may prefer having the back-to-back insert
statements in their code.
On Mon, Aug 25, 2008 at 11:28 AM, Tom Nugent <[EMAIL PROTECTED]> wrote:
> 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]
>>
>>
>
