Perl DBI and Foreign Keys

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

Replies are listed 'Best First'.
Re: Perl DBI and Foreign Keys by dsheroh (Monsignor) on Apr 08, 2019 at 07:57 UTC
`$dbh->last_insert_id` is generally the way to go, despite the caveats which seem to be putting you off. Unless you're trying to write something that's compatible with multiple database backends, you don't need to worry about them, you only have to make it work with the database you're using. (And if you are trying to target multiple db engines, you probably want to use a higher-level abstraction on top of DBI anyway, so that you don't have to deal with the inconsistencies of SQL syntax from one db engine to the next.) If you really don't think you can accept working with `last_insert_id`, then there's also the option of doing your insert, then `SELECT id FROM Participants WHERE Url = $the_url_i_just_inserted`, since you say the url is unique, but it seems a bit wasteful to do that extra query when `last_insert_id` is sitting right there, just waiting to be used for this exact purpose.	[reply] [d/l] [select]
Re^2: Perl DBI and Foreign Keys by Marshall (Canon) on Apr 08, 2019 at 10:05 UTC
Thanks! I wanted to hear from somebody who had used this function. My target DB's are MySQL and SQLite. If I have code that works with both of those DB's, I'm fine. It appears that last_insert_id will work with both of these DB's. I did consider the write, then read back to get the auto id, but that is rather goofy sounding and may not be possible in certain situations. I'm starting a new project using SQlite. The DB access is just a tiny part, but an important part.	[reply]
Re^3: Perl DBI and Foreign Keys by hdb (Monsignor) on Apr 08, 2019 at 12:30 UTC
And if you do not like last_insert_id, you can always build an index generator yourself, like `max( index_column ) + 1`.	[reply] [d/l]
Re^4: Perl DBI and Foreign Keys by Marshall (Canon) on Apr 08, 2019 at 12:45 UTC
Re^5: Perl DBI and Foreign Keys by soonix (Canon) on Apr 08, 2019 at 12:55 UTC
Some notes below your chosen depth have not been shown here
Re^3: Perl DBI and Foreign Keys by dsheroh (Monsignor) on Apr 09, 2019 at 07:29 UTC
I wanted to hear from somebody who had used this function. My target DB's are MySQL and SQLite. In that case, since it wasn't explicitly stated before: I have personally used `last_insert_id` with both MySQL and SQLite.	[reply] [d/l]
Re^3: Perl DBI and Foreign Keys by afoken (Chancellor) on Apr 09, 2019 at 18:37 UTC
See also ~~Re: SELECT LAST_INSERT_ID does not work~~ SELECT LAST_INSERT_ID does not work. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re: Perl DBI and Foreign Keys by poj (Abbot) on Apr 08, 2019 at 07:03 UTC
The "Url" for each ScoreCard is unique Use that to query the auto generated id `my ($id) = $dbh->selectrow_array ( 'SELECT id FROM ScoreCard WHERE Url = ?',undef,$Url);` [download] Update ; Perhaps also add UNIQUE KEY `URL` (`Url`) to your table ScoreCard create. poj	[reply] [d/l] [select]
Re^2: Perl DBI and Foreign Keys by Marshall (Canon) on Apr 08, 2019 at 07:24 UTC
Yes, of course that occured to me. {write ScoreCard, readback ScoreCard for that Url to get the unique id, write Participants, write Participants, write Participants, etc.} So what happens if I don't have a unique field like Url? In this case, I ScoreCard table did and I of course I used it to allow the tables to be joined. My application works...but, I am curious how to do this better and in other circumstances. Update: Perhaps think about it this way..I have a piece of paper A. It is like any other piece of paper that I have except that this particular piece of paper is associated with the finger prints from people X,Y,Z. I have other pieces of paper with other fingerprints. In this case the only thing that is unique about this piece of paper is that is a thing from a crime scene (will get evidence label of #1, #2,etc) on the bag. I asked this question to some Oracle gurus and there is a way to do this, but it is not standard SQL.	[reply]
Re^3: Perl DBI and Foreign Keys by hdb (Monsignor) on Apr 08, 2019 at 07:51 UTC
Well, there must be some piece of information that is the same in both tables. Otherwise, why would you even think about linking those tables? If not you need to create such a link, say a time stamp or a case number. If you have such a piece of information, you could put it in a third table, with AUTOINCREMENT index, and then use that index in both tables with the data.	[reply]
Re^4: Perl DBI and Foreign Keys by Marshall (Canon) on Apr 08, 2019 at 08:37 UTC
Re: Perl DBI and Foreign Keys by erix (Prior) on Apr 08, 2019 at 13:00 UTC
Postgres has a RETURNING clause on the INSERT/UPDATE/DELETE statements (`[1]`,`[2]`) which returns the inserted/updated/deleted values. Very handy. Oracle and Firebird can do something similar. Maybe lodge a ~~complaint~~ featurerequest with the MySql/MariaDB devs? `[1]` - PostgreSQL INSERT `[2]` - RETURNING	[reply] [d/l] [select]
Re: Perl DBI and Foreign Keys by bliako (Monsignor) on Apr 08, 2019 at 10:32 UTC
This may not be practical, but: do not tell the DB to create an autoincrement id for you. Instead give each new table an id YOU create and guaranteed to be unique. That will create you extra headaches: checking if the id you generated already exists in db, unless the generating algorithm guarantees no collisions given different table data. A random number generator does not guarantee uniqueness. How about creating such an id from millisecond time of insertion plus an autoincrement in case there are more than 1 insertions per millisecond. That wont work if you have more than 1 user entering data in parallel, unless you create an "autoincrement server" which is overkill.	[reply]
Re^2: Perl DBI and Foreign Keys by afoken (Chancellor) on Apr 09, 2019 at 19:07 UTC
This may not be practical, but: do not tell the DB to create an autoincrement id for you. Instead give each new table an id YOU create and guaranteed to be unique. That's commonly called a sequence and is often implemented in the DB server, guaranteed to deliver unique IDs under all circumstances. PostgreSQL Oracle MariaDB Other DB servers offer only an automatic ID generator, and for that, you need to use last_insert_id in DBI (see SELECT LAST_INSERT_ID does not work). MySQL MS SQL Server SQLite Related: Some very common errors (or, if you want to be polite, misconceptions): The ID generated by either of these mechanisms is JUST THAT. A unique value. In any usage scenario with more than one user and/or more than one process/thread and/or more than one CPU core, the next ID is not predictable. The ID is not a sort criteria. IDs are commonly set up to increment by one for each use. But that's an inplementation detail. They could also be set up to return multiples of seven starting from a ridiculously large positive number and going down to ridiculously large negative number. The returned IDs would still be unique. The bits from such a sequence could even be shuffled around so that any returned ID looks completely random (but still is unique). The numeric sequence of used IDs may have gaps, some times big gaps, e.g. after rolling back transactions. This is intentionally and by design to guarantee uniqueness. It does not hurt. It does not waste anything. Trying to fill those gaps is an error. The IDs in those gaps were once used, filling the gaps would reuse IDs and make them non-unique. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re^3: Perl DBI and Foreign Keys by bliako (Monsignor) on Apr 10, 2019 at 09:32 UTC
really useful to know, thanks, bliako	[reply]
Re^2: Perl DBI and Foreign Keys by Marshall (Canon) on Apr 08, 2019 at 11:19 UTC
The DB will create a unique id for each row whether I tell it do that or not. When I do an INSERT and then ask: `my $cur_id = $dbh->last_insert_id;` the DB gives me an id for my $dbh connection handle. This will work even if there are multiple writers to the DB. I should not increment that number or screw around with it in any way - it could be that 14 more inserts have happened in the meantime. This id number will be for the last insert that my connection did. I can use that number to write (INSERT) additional rows into another table within the DB. If I run the initial write and then id query and subsequent writes all as one transaction, then in theory with an ACID compliant DB, all will be fine. In practice that is not true because of the way the hardware works - different subject... There is no easier way to get a unique number than to ask the DB to do it for you. This avoids all sorts of complications like you mentioned. "autoincrement" does not mean that my id numbers, for my connection will be sequential. This just tells the DB to generate these numbers on its own.	[reply] [d/l]
Re^3: Perl DBI and Foreign Keys by bliako (Monsignor) on Apr 08, 2019 at 11:36 UTC
I agree, but you were concerned about the caveats of that approach, that's all. The DB will create a unique id for each row whether I tell it do that or not. I am under the impression that if a field is not `AUTO_INCREMENT` then it expects you to fill its value or it will take its default value if any. I like this article: https://medium.com/ingeniouslysimple/why-did-we-shift-away-from-database-generated-ids-7e0e54a49bb3	[reply] [d/l]
Re^4: Perl DBI and Foreign Keys by Marshall (Canon) on Apr 08, 2019 at 12:29 UTC
Re^5: Perl DBI and Foreign Keys by soonix (Canon) on Apr 08, 2019 at 14:32 UTC
Some notes below your chosen depth have not been shown here


We don't bite newbies here... much
	PerlMonks