SQLite: Howto Insert Foreign Key Values Into A Cross Reference Table

Question

Given the following data table definitions in SQLite Version 3.7.5:

CREATE TABLE [Books] (
  [Title] VARCHAR(125) NOT NULL, 
  [YearPublished] INT, 
  CONSTRAINT [CK_YearPublished] CHECK(YearPublished >= 1400));

CREATE TABLE [People] (
  [FirstName] VARCHAR(25) NOT NULL, 
  [LastName] VARCHAR(45) NOT NULL, 
  [MiddleName] VARCHAR(25), 
  CONSTRAINT [UQ_PersonName] UNIQUE([FirstName], [LastName], [MiddleName]));

CREATE TABLE [BooksAuthorsXRef] (
  [BookID] INT NOT NULL CONSTRAINT [FK_BookID] REFERENCES [Books]([rowid]), 
  [PersonID] INT NOT NULL CONSTRAINT [FK_PersonID] REFERENCES [People]([rowid]));

What SQL command can I use to populate the BookID and PersonID field values in the BooksAuthorsXRef data table to use the values for the rowid for a specific Title in the Books table to be associated with the rowid in the Persons table for a given LastName, FirstName, and MiddleName set of values?

I've already populated the Books table with one record;

INSERT INTO
       Books       
VALUES
      ('Stingray Shuffle', 2003);

And, the Persons table has been populated with the following record:

INSERT INTO 
       People
       (LastName, FirstName)       
VALUES
       ('Dorsey', 'Tim')      

What SQL command would I use in SQLite to define that Tim Dorsey (Persons rowid = 1) is the author of the book entitled "Stingray Shuffle" in the Books table (Books rowid = 1)?

Also, based on the data table definitions, are any changes needed to make adding and changing Books and Persons cross reference records in the BooksAuthorsXRef data table more managable and easier while maintaining some degree of referential integrity?

Thank you in advance for your time, help and patience.

Answer 1

If you've an existing table with all of the book + person data in it, you can do the following:

INSERT INTO BooksAuthorsXRef
SELECT      B.[rowid], P.[rowid]
FROM        XRefTemp T
INNER JOIN  Books B
ON          B.Title = T.Title
INNER JOIN  People P
ON          P.FirstName = T.FirstName
AND         COALESCE(P.MiddleName, '') = COALESCE(T.MiddleName, '')
AND         P.LastName = T.LastName

When inserting individual records, the last_insert_rowid() function will give you the rowid for the Book / People record you've just inserted.

To insert a record for an existing Book / Person, use:

INSERT INTO BooksAuthorsXRef
SELECT      B.[rowid], P.[rowid]
FROM        Books B
INNER JOIN  People P
ON          P.FirstName = 'TheFirstName'
AND         COALESCE(P.MiddleName, '') = 'TheMiddleName or empty if none'
AND         P.LastName = 'TheLastName'
WHERE       B.Title = 'TheBookTitle'