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




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