List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:January 5 2012 1:51pm
Subject:Re: Common Pattern for parent-child INSERTs?
View as plain text  
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

If you use InnoDB for your tables, you can use the ON UPDATE CASCADE option for your
foreign key constraints.

However, your habit is indeed a nasty one :-p It forces you to do joins where none are
necessary, thus needlessly slowing down operations.

The oo "extends" idea does not exactly match what you're doing, as the "child" table you
create does not inherit the parent's "attributes", it merely has it's own column and a
reference to the parent. This is good for 1:n relations, but for 1:1 there are only
downsides (except for a very few edge cases).




----- Original Message -----
> From: "Jan Steinman" <Jan@stripped>
> To: mysql@stripped
> Sent: Thursday, 5 January, 2012 1:12:15 AM
> Subject: 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!
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
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