Jan Steinman Common Pattern for parent-child INSERTs?
Jan 04, 2012; 16:12
Jan Steinman
Common Pattern for parent-child INSERTs?
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
Jan 05
Claudio Nanni Re: Common Pattern for parent-child INSERTs?
Jan 05, 2012; 01:48
Claudio Nanni
Re: Common Pattern for parent-child INSERTs?
Jan 04
Jan Steinman Re: Common Pattern for parent-child INSERTs?
Jan 04, 2012; 17:38
Jan Steinman
Re: Common Pattern for parent-child INSERTs?
Jan 05
Claudio Nanni Re: Common Pattern for parent-child INSERTs?
Jan 05, 2012; 03:04
Claudio Nanni
Re: Common Pattern for parent-child INSERTs?
Jan 05
Johan De Meersman Re: Common Pattern for parent-child INSERTs?
Jan 05, 2012; 14:51
Johan De Meersman
Re: Common Pattern for parent-child INSERTs?
Jan 06
Jan Steinman Re: Common Pattern for parent-child INSERTs?
Jan 06, 2012; 21:58
Jan Steinman
Re: Common Pattern for parent-child INSERTs?
Jan 07
Shawn Green (MySQL Re: Common Pattern for parent-child INSERTs?
Jan 07, 2012; 09:31
Shawn Green (MySQL
Re: Common Pattern for parent-child INSERTs?
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!