From: Claudio Nanni
Date: January 5 2012 12:48am
Subject: Re: Common Pattern for parent-child INSERTs?
List-Archive: http://lists.mysql.com/mysql/226561
Message-Id: <CAH7gepeaqF9cvwm60DBXgFzRpaV4KKf2dZnqBFPTq6Q8OeNs0Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 creati=
ng 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
> =A0 id INT NOT NULL AUTO INCREMENT PRIMARY KEY,
> =A0 name_first VARCHAR(255) NOT NULL,
> =A0 name_last VARCHAR(255) NOT NULL
>
> CREATE TABLE addresses TYPE InnoDB
> =A0 names_id INT NOT NULL REFERENCES names (id)
> =A0 street VARCHAR(255) NOT NULL,
> =A0 city VARCHAR(255) NOT NULL
>
> CREATE TABLE phones TYPE InnoDB
> =A0 names_id INT NOT NULL REFERENCES names (id)
> =A0 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 th=
ink I was violating the restrictions on VIEWs as stated in the manual.
>
> Is there a generalized pattern that is used for INSERTing and UPDATEing t=
hese parent-child tables? Does it require a TRIGGER in order to propagate t=
he 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 saf=
er in the long run than outright exposure. Life is either a daring adventur=
e, or nothing. -- Helen Keller
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql
>



--=20
Claudio