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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
