From: Jan Steinman
Date: January 5 2012 12:12am
Subject: Common Pattern for parent-child INSERTs?
List-Archive: http://lists.mysql.com/mysql/226560
Message-Id: <7E72D68D-4AC6-4412-865A-AB3BC2C12E0C@Bytesmiths.com>
MIME-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

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