in reply to Perl DBI and Foreign Keys

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.

Replies are listed 'Best First'.
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.

    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).

    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]

      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]

      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]
        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.
        Well... Not exactly...consider this code.. 
        #!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbfile = "whatever.sqlite";

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError =
+> 1})
    or die "Couldn't connect to database: " . DBI->errstr;


$dbh->do ("CREATE TABLE ScoreCard
             ( 
                Url       varchar(80)   DEFAULT '',
                DateTime  varchar(20)   DEFAULT '1995-12-30 00:00:01',
                Desc      varchar(100)  DEFAULT ''
             );
          ");

$dbh->do ("CREATE TABLE Participants
             (  id      integer PRIMARY KEY AUTOINCREMENT,
                Url     varchar(80)  ,
                Name    varchar(10)  DEFAULT ''
             );
          ");

        [download]
        You will see that the ScoreCard table winds up containing an extra field, "rowid" that I didn't specify in CREATE TABLE ScoreCard. Note: You need a program to show the actual created DB fields. That is a unique id that SQL will assign on it own. In the Participants TABLE, that field doesn't exist because I called it "id" and gave some rules for this PRIMARY KEY.

        I did take a look at your article and am still thinking about it. I did have to burst out laughing at this part:
        "We don’t want our complexity to grow linearly as we add more functionality into the system, which would drastically slow us down as we grow in the eyes of both business and value confidence." My gosh we only wish that complexity grew linearly with functionality. Complexity appears to grow exponentially with functionality. What does "growing in the eyes of value confidence" mean? What!?

[reply]
[d/l]

In Section Seekers of Perl Wisdom