List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:January 5 2012 2:04am
Subject:Re: Common Pattern for parent-child INSERTs?
View as plain text  
Jan,

Just thinking out loud. In relational theory views are just like any
entity, in this case the referential integrity would be with the same
logical entity,
and in the moment of the insert the referential integrity constraint
is violated because the new [names_id] will be present only after the
operations is done.
It might still be storage engine dependent and how it implements an
insert on a updatable view.

This said, at your own risk, you can disable the foreign key checks
before the insert and re-enable them after.

At your own risk.

Cheers

Claudio

2012/1/5 Jan Steinman <Jan@stripped>:
> 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 ::::
>
>
>
>



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