Editing foreign keys

A foreign key links a column in one table to the primary (or unique) key of another, so the database can guarantee that the reference always points to a row that exists. In the demo database, for example, every row in orders has a CustNo that should match a real customer in the customer table — a foreign key makes SQLite enforce that.

SQLite has supported foreign keys since version 3.6.19, and SQLite Expert enables them by default when it opens a database.

Foreign keys are managed on the Foreign Keys sub-tab of the Design tab.

To add a foreign key:

1. Click Add to open the Foreign Key Editor.

   

2. Set the foreign key's properties: constraint name, the parent table it references, the child and parent columns, and the ON DELETE, ON UPDATE, MATCH, DEFERRABLE, and INITIALLY clauses.

3. To choose the child or parent columns, click the corresponding … button and pick them in the column-selection dialog.

   

4. Click OK.

To modify a foreign key: select it, click Modify, adjust its properties, and click OK.

To delete a foreign key: select it, click Delete, and confirm.

Note

A single-column foreign key is written as a column constraint; one that spans several columns becomes a table constraint.

Warning

While a transaction is in progress, a table that is referenced by a foreign key from a table holding rows cannot be restructured: SQLite Expert restructures a table by rebuilding it under a temporary name, and the foreign keys cannot be switched off around that inside a transaction. Renaming a referenced column is not a restructure and is never restricted. See Limitations for the details.

See also: Editing primary keys · Limitations