#readwise
# SQLite Foreign Key Support
## Metadata
- Author: [[sqlite.org]]
- Full Title: SQLite Foreign Key Support
- URL: https://www.sqlite.org/foreignkeys.html
## Summary
SQLite supports foreign key constraints, which are used to enforce relationships between tables. By default, these constraints are disabled, and the application must enable them. Foreign key errors can occur if the parent table or key does not exist, or if the child table references them incorrectly. Advanced features, like `ON UPDATE CASCADE`, allow for more flexible management of these constraints during data updates and deletions.
## Highlights
**SQL foreign key constraints are used to enforce "exists" relationships between tables.** For example, consider a database schema created using the following SQL commands:
```sql
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
);
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER -- Must map to an artist.artistid!
);
```
The applications using this database are entitled to assume that for each row in the *track* table there exists a corresponding row in the artist table. After all, the comment in the declaration says so. Unfortunately, if a user edits the database using an external tool or if there is a bug in an application, rows might be inserted into the track table that do not correspond to any row in the artist table. Or rows might be deleted from the artist table, leaving orphaned rows in the track table that do not correspond to any of the remaining rows in artist. This might cause the application or applications to malfunction later on, or at least make coding the application more difficult.
One solution is to add an SQL foreign key constraint to the database schema to enforce the relationship between the artist and track table.
```sql
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER,
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
```
([View Highlight](https://read.readwise.io/read/01k2vpce37wq3418sd5kt9bzrg))
---
Attempting to insert a row into the track table that does not correspond to any row in the artist table will fail, as will attempting to delete a row from the artist table when there exist dependent rows in the track table There is one exception: if the foreign key column in the track table is `NULL`, then no corresponding entry in the artist table is required. ... If the application requires a stricter relationship between artist and track, where `NULL` values are not permitted in the trackartist column, simply add the appropriate `NOT NULL` constraint to the schema.
---
**Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the [PRAGMA foreign_keys](https://www.sqlite.org/foreignkeys.html/pragma.html#pragma_foreign_keys) command.** ^sll1kt
```sql
PRAGMA foreign_keys = ON;
```
^gmyorn
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each [database connection](https://www.sqlite.org/foreignkeys.html/c3ref/sqlite3.html). (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. ([View Highlight](https://read.readwise.io/read/01k2vppx9zw3j4v98xne1nxv0n))
---
### `ON DELETE` and `ON UPDATE` Actions
Foreign key `ON DELETE` and `ON UPDATE` clauses are used to configure actions that take place when deleting rows from the parent table (`ON DELETE`), or modifying the parent key values of existing rows (`ON UPDATE`). A single foreign key constraint may have different actions configured for `ON DELETE` and `ON UPDATE`. Foreign key actions are similar to triggers in many ways.
The `ON DELETE` and `ON UPDATE` action associated with each foreign key in an SQLite database is one of `NO ACTION`, `RESTRICT`, `SET NULL`, `SET DEFAULT` or `CASCADE`. If an action is not explicitly specified, it defaults to `NO ACTION`.
| Action | Description |
| ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `NO ACTION` | Configuring `NO ACTION` means just that: when a parent key is modified or deleted from the database, no special action is taken. |
| `RESTRICT` | The `RESTRICT` action means that the application is prohibited from deleting (for `ON DELETE RESTRICT`) or modifying (for `ON UPDATE RESTRICT`) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a `RESTRICT` action and normal foreign key constraint enforcement is that the `RESTRICT` action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a `RESTRICT` action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified. |
| `SET NULL` | If the configured action is `SET NULL`, then when a parent key is deleted (for `ON DELETE SET NULL`) or modified (for `ON UPDATE SET NULL`), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL `NULL` values. |
| `SET DEFAULT` | The `SET DEFAULT` actions are similar to `SET NULL`, except that each of the child key columns is set to contain the column's default value instead of `NULL`. Refer to the [CREATE TABLE](https://www.sqlite.org/lang_createtable.html) documentation for details on how default values are assigned to table columns. |
| `CASCADE` | A `CASCADE` action propagates the delete or update operation on the parent key to each dependent child key. For an `ON DELETE CASCADE` action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an `ON UPDATE CASCADE` action, it means that the values stored in each dependent child key are modified to match the new parent key values. |
([View Highlight](https://read.readwise.io/read/01k2vpxw2f1ya7eddw8nmjbh2j))
---