Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Re^3: Perl DBI and Foreign Keys

by bliako (Monsignor)
on Apr 08, 2019 at 11:36 UTC ( [id://1232290]=note: print w/replies, xml ) Need Help??


in reply to Re^2: Perl DBI and Foreign Keys
in thread Perl DBI and Foreign Keys

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

Replies are listed 'Best First'.
Re^4: Perl DBI and Foreign Keys
by Marshall (Canon) on Apr 08, 2019 at 12:29 UTC
    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 '' ); ");
    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!?

      You will see that the ScoreCard table winds up containing an extra field, "rowid" that I didn't specify in CREATE TABLE ScoreCard.
      That is SQLite specific, documented under https://sqlite.org/lang_createtable.html#rowid
      Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. …
      BTW SQLite's last_insert_rowid() documentation explicitly refers to "the database connection which invoked the function", so should work with multiple parallel insertions.
        Yeah, rowid is SQLite-specific, but most[citation needed] major database engines will generate some kind of hidden unique identifier for each row if there isn't one explicitly specified. I believe this is because it's much harder to implement joins between tables which completely lack any way to uniquely identify rows. (I read something along those lines many years ago, but haven't really looked into it in any detail.) IIRC, MySQL and Postgres use UUIDs for this purpose and, of course, the field names involved will be engine-specific (if they're user-accessible at all).

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1232290]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (4)
As of 2024-03-29 11:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found