Foreign Keys Support in SQLite Manager

A foreign key constraint specifies that for each record in a "child" table containing a foreign key that there must also be a unique record that matches the key in the referenced or "parent" table. Generally a foreign key will be related to the primary key of the parent table, but it doesn't necessarily have to be that way. It is important however that the related column(s) in the parent table must have a UNIQUE constraint otherwise a Cartesian Product will likely result.

Since version 3.6.19, the SQLite database engine has included the capability to enforce Foreign Key constraints but this functionality is disabled by default.

Enabling Foreign Key Support

Foreign Keys can be enabled manually for each database session by going to the DB Settings tab and setting the Foreign Keys switch to ON.

To enable Foreign Key support automatically when a database is opened.

1. Go to the Tools drop down menu in the Menu bar and select Open On-connect SQL Tab.
2. At the On-Connect SQL Tab enter the following line to the text box on the tab.

PRAGMA foreign_keys=ON;

Format for CREATE TABLE Statement with FOREIGN KEY

One to Many Relationship

CREATE TABLE child_table_name (field_1 INTEGER PRIMARY KEY, field_2 TEXT, foreign_key_field INTEGER , FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name(parent_key_field));

One to One relationship

For a parent and a child table to have a one to one relationship the FOREIGN KEY column in the child table must have UNIQUE constraint.

CREATE TABLE child_table_name (field_1 INTEGER PRIMARY KEY, field_2 TEXT, foreign_key_field INTEGER UNIQUE , FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name (parent_key_field));

FOREIGN KEY PHRASE with CASCADE

Assuming that foreign key support has been enabled for the session. Foreign key constraints will prevent you from changing the key column in the parent table or deleting records in the parent table which have related records in the child table without first updating the child table. Appending ON DELETE CASCADE and ON UPDATE CASCADE will cause the SQLite Database Engine to make the necessary changes to the child table automatically.

ON DELETE CASCADE appended after the FOREIGN KEY definition will cause records in the Child table to be deleted when a matching parent id is deleted.

When ON UPDATE CASCADE is used, the foreign key field in the child table will be updated to match the record(s) in the parent table.

FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name(parent_key_field) ON DELETE CASCADE ON UPDATE CASCADE );

Add a Foreign Key Constraint to an Existing Table

Ideally, any necessary Foreign Key constraint should have been included when a table was created.

That being said, it is possible at the present time, to use SQLite Manager to "shoehorn" a foreign key into a table where none exists by the following method.

Important - Before attempting to ALTER the structure of any table that contains valuable data,make a back up copy of that table first.

SQLite has only limited ALTER TABLE support which means that whenever SQLite Manager is making any changes to a table other than renaming it or adding a column, it is essentially dropping the table and recreating it. Also to successfully add a Foreign Key to an existing table that already contains data, the table must not contain any orphan records which will conflict with this constraint.

1. In the Columns text box, right click on the last column name listed to bring up the context menu and select Edit Column.

Note that if the last column in the TABLE definition is the PRIMARY KEY then it will be necessary to first add a new column and then edit the column type of the new column in order to add the FOREIGN KEY definition.

2. Within the Column Type box , append a comma and the FOREIGN KEY definition after data type.
3. Click on the Change button and then click the Yes button on the Dangerous Operation dialog box.