|Do you know where your variables are?
Perl DBI and Foreign Keysby Marshall (Canon)
|on Apr 08, 2019 at 02:28 UTC
Marshall has asked for the wisdom of the Perl Monks concerning the following question:
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.
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!