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