List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:January 5 2012 1:38am
Subject:Re: Common Pattern for parent-child INSERTs?
View as plain text  
Thanks, Claudio. What you suggested is essentially what I'm doing. I just thought if this
were something common, someone would have a better way of doing it. I would LOVE to be
able to simply insert into a names-addresses-phones VIEW, but I haven't been able to make
that work.

On 4 Jan 12, at 16:48, Claudio Nanni wrote:

> Hi Jan,
> 
> I am not sure to understand what your question is,
> what do you mean with inserting updating 2-3 tables?
> I guess treat the 3-tables join as one single 'object' ?
> 
> Since you have the referential integrity constraint on the [addresses]
> and [phones] table you need to follow this basic pattern:
> 
> INSERT:
> 
> 1.insert the record into [names]
> 2.insert the records into [addresses] and [phones]
> 
> DELETE:
> 1.delete the records from [addresses] and [phones]
> 2.delete the record from [names]
> 
> UPDATE:
> (a)no problem if you don't update the foreign keys (i.e. assigning an
> address and/or a phone number to another person)
> (b)if you need to update the foreign keys just make sure you set them
> to an existing names_id
> 
> The problem you mention with the view is probably coming from the fact
> that when you insert into a view although theoretically possible if
> the underlying select is a simple multi-table join (updatable view)
> you have no assurance on the order of the inserts inside the view, it
> is probably depending on the specific storage engine implementation.
> 
> I hope this shed a bit of light.
> 
> Claudio
> 
> 2012/1/5 Jan Steinman <Jan@stripped>:
>> Having been steeped in object-orientation, I have a nasty habit of creating
> parent-child tables that have a 1:1 relationship where the child extends the parent,
> sometimes to a depth of three or more.
>> 
>> For example:
>> 
>> CREATE TABLE names TYPE InnoDB
>>   id INT NOT NULL AUTO INCREMENT PRIMARY KEY,
>>   name_first VARCHAR(255) NOT NULL,
>>   name_last VARCHAR(255) NOT NULL
>> 
>> CREATE TABLE addresses TYPE InnoDB
>>   names_id INT NOT NULL REFERENCES names (id)
>>   street VARCHAR(255) NOT NULL,
>>   city VARCHAR(255) NOT NULL
>> 
>> CREATE TABLE phones TYPE InnoDB
>>   names_id INT NOT NULL REFERENCES names (id)
>>   phone VARCHAR(255) NOT NULL
>> 
>> (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax
> is not what I'm here about.)
>> 
>> Now how do I go about INSERTing or UPDATEing two or three tables at once in a way
> that maintains referential integrity?
>> 
>> I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I
> was violating the restrictions on VIEWs as stated in the manual.
>> 
>> Is there a generalized pattern that is used for INSERTing and UPDATEing these
> parent-child tables? Does it require a TRIGGER in order to propagate the foreign key?
>> 
>> (BTW: MySQL version 5.0.92, if that matters...)
>> 
>> Thanks in advance for any help offered!
>> 
>> ----------------
>> Security is mostly a superstition. Security does not exist in nature, nor do the
> children of men as a whole experience it. Avoiding danger is no safer in the long run than
> outright exposure. Life is either a daring adventure, or nothing. -- Helen Keller
>> :::: Jan Steinman, EcoReality Co-op ::::
>> 
>> 
>> 
>> 
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql
>> 
> 
> 
> 
> -- 
> Claudio

----------------
If they can get you asking the wrong questions, they don't have to worry about the
answers. -- Thomas Pynchon
:::: Jan Steinman, EcoReality Co-op ::::




Thread
Common Pattern for parent-child INSERTs?Jan Steinman5 Jan
  • Re: Common Pattern for parent-child INSERTs?Claudio Nanni5 Jan
    • Re: Common Pattern for parent-child INSERTs?Jan Steinman5 Jan
      • Re: Common Pattern for parent-child INSERTs?Claudio Nanni5 Jan
  • Re: Common Pattern for parent-child INSERTs?Johan De Meersman5 Jan
    • Re: Common Pattern for parent-child INSERTs?Jan Steinman7 Jan
      • Re: Common Pattern for parent-child INSERTs?MySQL)7 Jan