Thanks, Claudio. What you suggested is essentially what I'm doing. I just thought if this were something common, someone would have a better way of doing it. I would LOVE to be able to simply insert into a names-addresses-phones VIEW, but I haven't been able to make that work.
On 4 Jan 12, at 16:48, Claudio Nanni wrote: > Hi Jan, > > I am not sure to understand what your question is, > what do you mean with inserting updating 2-3 tables? > I guess treat the 3-tables join as one single 'object' ? > > Since you have the referential integrity constraint on the [addresses] > and [phones] table you need to follow this basic pattern: > > INSERT: > > 1.insert the record into [names] > 2.insert the records into [addresses] and [phones] > > DELETE: > 1.delete the records from [addresses] and [phones] > 2.delete the record from [names] > > UPDATE: > (a)no problem if you don't update the foreign keys (i.e. assigning an > address and/or a phone number to another person) > (b)if you need to update the foreign keys just make sure you set them > to an existing names_id > > The problem you mention with the view is probably coming from the fact > that when you insert into a view although theoretically possible if > the underlying select is a simple multi-table join (updatable view) > you have no assurance on the order of the inserts inside the view, it > is probably depending on the specific storage engine implementation. > > I hope this shed a bit of light. > > Claudio > > 2012/1/5 Jan Steinman <j...@bytesmiths.com>: >> 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 >> > > > > -- > Claudio ---------------- If they can get you asking the wrong questions, they don't have to worry about the answers. -- Thomas Pynchon :::: Jan Steinman, EcoReality Co-op :::: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
