DBI & Multiple Table Inserts, Foreign Key Inserts and Transactions

awohld has asked for the wisdom of the Perl Monks concerning the following question:

I've read the DBI documentation on CPAN and ordered "Programming the Perl DBI" but haven't received it yet. My Perl skills are mediocre so I'm looking for some suggestions

I'm making a database app with MySQL and there are many tables with foreign key references. I'm using InnoDB tables that support transactions.

Say I have some tables called "People", "Address", and "LivesAt". "People" to "Address" is a many to many relationship and "LivesAt" has the foreign keys of "People" and "Address".

So what I'm planning on doing is to add a person to "People" and call "$rv = $dbh->last_insert_id;" to get the persons primary key to use as the foreign key in "LivesAt".

But say I add a person to the database then someone else milliseconds later adds another person to the database, and then I execute "$rv = $dbh->last_insert_id;", would that give me not my insert_id but the other persons insert_id? If this would give me an improper insert_id then I'll use transactions.

This may be far fetched, but I've looked all over and haven't seen any answers. Is there any other way to update multiple tables that have related foreign/primary keys and keep the foreign keys consistent besides doing an update, then getting the "last_insert_id" and using that as the foreign key for related tables?

In the "Perl Cookbook 2nd Edition" it says you can do "inserts, updates, deletes, queries here" on page 573 in the eval statement. Does this mean you can do SELECT statements while in the middle of a transaction? I'd like to do this to get the foreign key values for my new INSERTS.

Summary of Questions:
1. Say I add a person to the database then someone else milliseconds later adds another person to the database, and then I execute "$rv = $dbh->last_insert_id;", would that give me not my insert_id but the other persons insert_id from their insert?

2. Is there any other way to update multiple tables that have related foreign/primary keys and keep the foreign keys consistent besides doing an update, then getting the "last_insert_id" and using that as the foreign key for related tables?

3. Can I do SELECT statements while in the middle of a transaction?

Comment on DBI & Multiple Table Inserts, Foreign Key Inserts and Transactions

Replies are listed 'Best First'.
Re: DBI & Multiple Table Inserts, Foreign Key Inserts and Transactions by demerphq (Chancellor) on Sep 07, 2005 at 06:45 UTC
1. Say I add a person to the database then someone else milliseconds later adds another person to the database, and then I execute "$rv = $dbh->last_insert_id;", would that give me not my insert_id but the other persons insert_id from their insert? No, not normally anyway. Normally last_insert_id is maintained per connection. So if one user inserts on one connection and you insert on yours they will both have different, and correct, ids. 2. Is there any other way to update multiple tables that have related foreign/primary keys and keep the foreign keys consistent besides doing an update, then getting the "last_insert_id" and using that as the foreign key for related tables? Well assuming you are using autoidentity columns, then no not really. Some people use storedprocs but that just shifts the logic to a different place. Can I do SELECT statements while in the middle of a transaction? Yes. None of these questions are perl questions, and the exact particulars of the answers will be heavily dependent on the RDMBS that you are using. For instance the select question is really more complex than you think. If some other process has locked the table then your query will block, and may even deadlock and be killed by the DB. Also I assume you are a new poster here. Please dont use bold text in such an indiscriminate way. If you want to emphasize a word or something then no problem, but half your post is in bold, which only encourages headaches in your readership, something that is not wise in a post seeking wisdom. --- $world=~s/war/peace/g	[reply]
Re: DBI & Multiple Table Inserts, Foreign Key Inserts and Transactions by dragonchild (Archbishop) on Sep 08, 2005 at 13:17 UTC
Depending on the version of MySQL, you can do updates against multiple tables. However, if you're having to update multiple tables and change the FK's, then you're doing something really weird. The FKs are just there to maintain integrity. If you're altering FKs, then you're losing integrity. If you're needing last_insert_id for FK'ed tables, that means you're creating the child before the parent, which also makes no sense. Or, am I missing something? My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]