SQLite: Howto Insert Foreign Key Values Into A Cross Reference Table
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.
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'