StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQLite: Howto Insert Foreign Key Values Into A Cross Reference Table
primarykey
Id
6086175
data
AcceptedAnswerId
6086202
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-05-22T05:02:28.547
FavoriteCount
0
LastActivityDate
2011-05-22T14:42:10.083
LastEditDate
2011-05-22T11:32:42.050
LastEditorUserId
179902
OwnerUserId
179902
ParentId
0
PostTypeId
1
Score
0
ViewCount
6136
LastEditorDisplayName
text
Body
Given the following data table definitions in SQLite Version 3.7.5: <pre><code>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])); </code></pre> 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; <pre><code>INSERT INTO Books VALUES ('Stingray Shuffle', 2003); </code></pre> And, the Persons table has been populated with the following record: <pre><code>INSERT INTO People (LastName, FirstName) VALUES ('Dorsey', 'Tim') </code></pre> 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. 
Tags
<sql><sqlite><sqlite3>
Title
SQLite: Howto Insert Foreign Key Values Into A Cross Reference Table
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USClockEndGooner
UserOwnerUserId
1. USClockEndGooner
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.