When you create a table in SQLite, you can also create a foreign key in order to establish a relationship with another table.

This article provides an example of creating a foreign key when creating a table in SQLite.

Enable Foreign Key Support

The first thing we should do is to enable foreign key support (if it hasn’t already been done).

Assuming your SQLite library hasn’t been compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined, you will still need to enable foreign key support at runtime.

To do this, run the following statement:

PRAGMA foreign_keys = ON;

This will enable foreign key enforcement for your database connection.

If you open another connection, you’ll need to run the same statement for that connection.

Note that this setting is not required for creating foreign keys, but it is required for enforcing foreign keys.

Now that we’ve enabled foreign key support, let’s go ahead and create a foreign key.

Example

Imagine we want two tables with the following data.

Table called Pets:

PetId       PetName     TypeId    
----------  ----------  ----------
1           Brush       3         
2           Tweet       3         
3           Yelp        1         
4           Woofer      1         
5           Fluff       2         

Table called Types:

TypeId      Type      
----------  ----------
1           Dog       
2           Cat       
3           Parakeet  
4           Hamster   

And we want the TypeId column of the Pets table to reference the TypeId column of the Types table.

In other words, we want to make Pets.TypeId the child key (with a foreign key constraint), and Types.TypeId the parent key (with a primary key constraint).

While parent keys are usually also the primary key for the table, this is not actually a requirement. In this example we’ll make it the primary key.

We can use the following code to create these two tables.

CREATE TABLE Types( 
    TypeId INTEGER PRIMARY KEY, 
    Type
);

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(TypeId)
);

The part that creates the foreign key is this:

FOREIGN KEY(TypeId) REFERENCES Types(TypeId)

The FOREIGN KEY(TypeId) part declares Pets.TypeId as the foreign key.

Although I didn’t qualify the column name with its table name, we know it’s Pets.TypeId (and not Types.TypeId) because we’re running this in the CREATE TABLE statement for Pets.

The REFERENCES Types(TypeId) specifies the column that our foreign key will reference. In this case it will reference the TypeId column of the Types table.

Now that our tables have been created with the appropriate foreign key, we can add data.

INSERT INTO Types VALUES 
    ( NULL, 'Dog' ),
    ( NULL, 'Cat' ),
    ( NULL, 'Parakeet' ),
    ( NULL, 'Hamster' );

INSERT INTO Pets VALUES 
    ( NULL, 'Brush', 3 ),
    ( NULL, 'Tweet', 3 ),
    ( NULL, 'Yelp', 1 ),
    ( NULL, 'Woofer', 1 ),
    ( NULL, 'Fluff', 2 );

The tables now contain the data shown above.

sqlite> SELECT * FROM Pets;
PetId       PetName     TypeId    
----------  ----------  ----------
1           Brush       3         
2           Tweet       3         
3           Yelp        1         
4           Woofer      1         
5           Fluff       2         
sqlite> SELECT * FROM Types;
TypeId      Type      
----------  ----------
1           Dog       
2           Cat       
3           Parakeet  
4           Hamster   

Foreign Key Violation

But now let’s try inserting data that violates the foreign key.

Let’s try to add a pet that uses a non-existent TypeID (i.e. a TypeId value that doesn’t exist in the Types column).

INSERT INTO Pets VALUES 
    ( NULL, 'Homer', 5 );

Result:

Error: FOREIGN KEY constraint failed

So our foreign key successfully prevented bad data from entering the database. It therefore, helped us maintain data integrity.

If you don’t get this error, and the data was successfully inserted, you haven’t enabled foreign key support. As mentioned, you’ll need to enable foreign key support before your foreign keys will be enforced.

Adding a Foreign Key to an Existing Table

The ALTER TABLE statement in SQLite is very limited, and it doesn’t allow for adding a foreign key to an existing table.

Therefore, if you need to add a foreign key to an existing table, you’ll need to drop the table and create it again with the foreign key constraint.

If the table contains data that you want to keep, you can transfer that data to another table, before transferring it back once you’ve created the new table with the foreign key constraint.