androidsqlsqliteforeign-key-relationshipcreate-table

SQLite Foreign Key


I'm following the instructions from the SQLite documentation at http://www.sqlite.org/foreignkeys.html however my attempt to add a foreign key is failing. Here are my create statements:

CREATE TABLE 
    checklist (
        _id INTEGER PRIMARY KEY AUTOINCREMENT, 
        checklist_title TEXT,
        description TEXT,
        created_on INTEGER, 
        modified_on INTEGER
    );

CREATE TABLE 
    item (
        _id INTEGER PRIMARY KEY AUTOINCREMENT,  
        FOREIGN KEY(checklist_id) REFERENCES checklist(_id), 
        item_text TEXT, item_hint TEXT, 
        item_order INTEGER, 
        created_on INTEGER, 
        modified_on INTEGER
    );

The first table is made fine. The error occurs in the second statement. I have tried both with wrapping the two queries in a transaction and without. Here is the error:

unknown column "checklist_id" in foreign key definition (code 1): , while compiling: CREATE TABLE item (_id INTEGER PRIMARY KEY AUTOINCREMENT, FOREIGN KEY(checklist_id) REFERENCES checklist(_id), item_text TEXT, item_hint TEXT, item_order INTEGER, created_on INTEGER, modified_on INTEGER)


Solution

  • You still have to create the column checklist_id INTEGER before you add it as a Foreign key.

    So it would be:

    CREATE TABLE 
        checklist (
            _id INTEGER PRIMARY KEY AUTOINCREMENT, 
            checklist_title TEXT,
            description TEXT,
            created_on INTEGER, 
            modified_on INTEGER
        );
    
    CREATE TABLE 
        item (
            _id INTEGER PRIMARY KEY AUTOINCREMENT,  
            checklist_id INTEGER,
            item_text TEXT, 
            item_hint TEXT, 
            item_order INTEGER, 
            created_on INTEGER, 
            modified_on INTEGER,
            FOREIGN KEY(checklist_id) REFERENCES checklist(_id)
        );