List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:January 5 2012 12:48am
Subject:Re: Common Pattern for parent-child INSERTs?
View as plain text  
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
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