O.k., this question is more about the best way to run a multi-table insert on a MySQL DB (4.0.18), given a console based interface (Python 2.3.x under SUSE Linux 9.2). My guess is that this would be a bit easier with a GUI, where I would have widgets to assign to various functions. However, I am currently building a console app. The GUI will come later.
When I add a person to the DB, the main table impacted is called 'person'. However, a few other tables are affected as well - entity, address, affiliation, and aux_mbr. For example, the person table uses the keys from the others: person.affil_id = affil.affil_id, person.ent_id = entity.ent_id, etc. Somehow, the program will need to get the foreign keys from the other tables, and insert that into the person table. Technically, the program won't be very likely to know what the current primary_key is for each table. The 2 options I see so far are: (1) run insert queries on each table, ignoring the foreign key fields, and then running an update query on person to add the foreign keys from the respective tables: insert into person values (PK, 'blah', 'blab') insert into entity values (PK, 'bleep', 'blip') update person set ent_id=1 where person_id=1 #'1' should be a variable (2) run a query when the input form is launched to determine the current max value of the foreign keys, then use that info to automatically add the foreign keys into the person table: select entity_id MAX from entity ### run Python input form, ent_id is passed to the appropriate input statement ### insert into person tuple #(PK, ent_id, 'blah', 'blab') The commands here aren't intended to be precise, but rather to help paint the picture. A link to a previous thread or documentation on this would be fine. I don't mind doing the reading - but my Google search seems to turn up more info about multi-table deletes than inserts. I realize that MySQL doesn't support a single multi-table insert function, but are the two approaches I see good, bad, common?? Thanks, Don -- evangelinux GNU Evangelist http://matheteuo.org/ http://chaddb.sourceforge.net/ "Free software is like God's love - you can share it with anyone anytime anywhere." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
