iOS SQLite: How to automatically set foreign key?

Question

My app uses SQLite, and I've sorted out the create table statements. The idea is that table A and B have a one-to-many (or one) relationship, so the foreign key will be in Table B. Now I know about autoincrement for creating the primary key but how will this work for the foreign key? What if I add one row for Table A and 5 rows for Table B (all of which ideally are linked to that single row in Table A)? Won't it just autoincrement from 001-005 in table B?

Answer 1

But before you can set the foreign keys, you have to turn it ON first. Do this first instead,

PRAGMA foreign_keys=ON;

Answer 2

Yes, if it's one-to-many between A and B, and as you add records in B, you will auto increment B's primary key, but not the foreign key to A (assuming you make it a plain old INTEGER with no AUTOINCREMENT). Given your example, yes, B will have five records, 1-5, which will all point to record 1 in A. So, when you add the record in A, you grab its id via FMDB's lastInsertRowId (or sqlite's sqlite3_last_insert_rowid()), store that in a variable, and then use that when populating the foreign key in B. So, in short, you don't "automatically" set the foreign key, but just do so manually, but it isn't hard.

In terms of how to configure the tables, maybe it helps if we look at an example of a one to many relationships between authors and books (ignoring the possibility of co-authorship, but focusing on the fact that one author can write multiple books). Thus, you have two entities, a author (A) entity, and a book (B) entity. The book.book_author_id is a foreign key referencing author.author_id.

Thus, would look like:

CREATE TABLE author
(
    author_id INTEGER PRIMARY KEY AUTOINCREMENT,
    author_last_name TEXT,
    author_first_name TEXT
);

CREATE TABLE book
(
    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    book_author_id INTEGER,
    FOREIGN KEY (book_author_id) REFERENCES author (author_id)
);

INSERT INTO author (author_last_name, author_first_name) VALUES ('William', 'Shakespeare');

INSERT INTO book (title, book_author_id) VALUES ('Hamlet', 1);
INSERT INTO book (title, book_author_id) VALUES ('Macbeth', 1);
INSERT INTO book (title, book_author_id) VALUES ('Othello', 1);
INSERT INTO book (title, book_author_id) VALUES ('King Lear', 1);
INSERT INTO book (title, book_author_id) VALUES ('Henry V', 1);

And if we look at the results, it looks like:

$ sqlite3 test.db
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column
sqlite> .headers on
sqlite> 
sqlite> CREATE TABLE author
   ...> (
   ...>     author_id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>     author_last_name TEXT,
   ...>     author_first_name TEXT
   ...> );
sqlite> 
sqlite> CREATE TABLE book
   ...> (
   ...>     book_id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>     title TEXT,
   ...>     book_author_id INTEGER,
   ...>     FOREIGN KEY (book_author_id) REFERENCES author (author_id)
   ...> );
sqlite> 
sqlite> INSERT INTO author (author_last_name, author_first_name) VALUES ('William', 'Shakespeare');
sqlite> 
sqlite> SELECT * FROM author;
author_id   author_last_name  author_first_name
----------  ----------------  -----------------
1           William           Shakespeare      
sqlite> 
sqlite> INSERT INTO book (title, book_author_id) VALUES ('Hamlet', 1);
sqlite> INSERT INTO book (title, book_author_id) VALUES ('Macbeth', 1);
sqlite> INSERT INTO book (title, book_author_id) VALUES ('Othello', 1);
sqlite> INSERT INTO book (title, book_author_id) VALUES ('King Lear', 1);
sqlite> INSERT INTO book (title, book_author_id) VALUES ('Henry V', 1);
sqlite> 
sqlite> SELECT * FROM book;
book_id     title       book_author_id
----------  ----------  --------------
1           Hamlet      1             
2           Macbeth     1             
3           Othello     1             
4           King Lear   1             
5           Henry V     1             
sqlite> .quit

Or, if you want to do it programmatically (I'm using FMDB which makes this much simpler, but clearly the same logic works if you're doing your own sqlite3 calls, but it just takes a lot more code):

- (void)createAuthorTable
{
    BOOL result = [_db executeUpdate:
                     @"CREATE TABLE IF NOT EXISTS author "
                      "("
                          "author_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                          "author_last_name TEXT, "
                          "author_first_name TEXT "
                      ");"];
    NSAssert(result, @"%s - Unable to create author table", __FUNCTION__);
}

- (void)createBookTable
{
    BOOL result = [_db executeUpdate:
                     @"CREATE TABLE IF NOT EXISTS book "
                      "("
                          "book_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                          "title TEXT, "
                          "book_author_id INTEGER, "
                          "FOREIGN KEY (book_author_id) REFERENCES author (author_id) "
                      ");"];
    NSAssert(result, @"%s - Unable to create book table", __FUNCTION__);
}

- (sqlite_int64)createAuthorWithFirstName:(NSString *)firstName lastName:(NSString *)lastName
{
    BOOL result = [_db executeUpdate:@"INSERT INTO author (author_first_name, author_last_name) VALUES (?, ?)", firstName, lastName];
    NSAssert(result, @"%s - Unable to insert author record", __FUNCTION__);

    return [_db lastInsertRowId];
}

- (sqlite_int64)createBookWithTitle:(NSString *)title authorId:(sqlite_int64)authorId
{
    BOOL result = [_db executeUpdate:@"INSERT INTO book (title, book_author_id) VALUES (?, ?)", title, [NSNumber numberWithInt:authorId]];
    NSAssert(result, @"%s - Unable to insert book record", __FUNCTION__);

    return [_db lastInsertRowId];
}

- (void)testInsert
{
    [self openDatabase];

    NSArray *bookTitles = [NSArray arrayWithObjects:@"Hamlet", @"Macbeth", @"Othello", @"King Lear", @"Henry V", nil];
    [self createAuthorTable];
    [self createBookTable];

    sqlite_int64 authorId = [self createAuthorWithFirstName:@"William" lastName:@"Shakespeare"];

    sqlite_int64 bookId;
    for (NSString *bookTitle in bookTitles)
        bookId = [self createBookWithTitle:bookTitle authorId:authorId];

    [self closeDatabase];
}

Answer 3

A column with a foreign key constraint is not typically an autoincrementing value but refers to a (pre-existing) key value in another table. So if your AUTHORS table has an autoincrementing integer primary key, your TITLES.AuthorID column would simply be an integer with no autoincrement capability.

BTW, integer values do not have leading zeroes: 001-005 -- that usually implies a zero-padding which must be represented with a text/varchar datatype.