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 ::::
