This question involves both Perl DBI and SQL.
I don't know how to implement a correct SQL write to multiple tables using a Foreign Key where the foreign key is unknown until after the first table is written (i.e the Foreign Key is a DB AUTOINCREMENT value) in the primary table).

I did sucessfully implement an approach similar to a Foreign Key in an SQLite DB using Perl.
Let me attempt to explain further with some abreviated code:

$dbh->do ("CREATE TABLE ScoreCard
             (  id           integer PRIMARY KEY AUTOINCREMENT,
                Url          varchar(80)   NOT NULL,
                DateTime     varchar(20)   DEFAULT '1995-12-30 00:00:0
+1',
                Description  varchar(80)   NOT NULL
             );
          ");

$dbh->do ("CREATE TABLE Participants
             (  id           integer PRIMARY KEY AUTOINCREMENT,
                Url          varchar(80)  NOT NULL REFERENCES ScoreCar
+d(Url) ON DELETE CASCADE,
                Name         varchar(10)  NOT NULL
             );
          ");

[download]
In the above structures, I used the "Url" as the psuedo "foreign key" between tables.
Each Scorcard has at least one and perhaps many particpants.
The "Url" for each ScoreCard is unique. So I used that for entries into the Participants table.
The Url "http://blah/blah/page1289" would be entered 9 times into the Participants table for 9 Names related to that ScoreCard.

The logical link between the tables would be the integer "id" of the ScoreCard entry. Url doesn't have to be in the Participants table. But how would I know this id for the Particpants write? There is a DBI method: "last_insert_id" but there are a lot of DB specific caveats for that method.

Hints about how to go about this would be appreciated!

In reply to Perl DBI and Foreign Keys by Marshall

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.