SQL Foreign Key

A foreign key is used to link records in two tables. It acts as a reference between the primary key in one table and a column in another. This ensures referential integrity, meaning records in one table must relate to valid entries in another.

Tutorials dojo strip

SQL Foreign Key Syntax

SQL

SQLite does NOT support ALTER TABLE to add a FOREIGN KEY directly.
You must create a new table with the constraint included.

SQL Foreign Key Example

SQL Foreign Key on Create Table

This query shows how to define a foreign key at the time of table creation. This ensures that every prescription entry is tied to a valid patient_id from the Patients table.

SQL

SQL Foreign Key on Alter Table

SQLite does not support adding a foreign key using ALTER TABLE. The only workaround is recreate the table with the foreign key.

Steps:

  1. Create a new table with the foreign key.
  2. Copy data from the old table.
  3. Drop the old table.
  4. Rename the new one.

SQL Foreign Key Drop a Foreign Key Constraint

SQLite also does not support ALTER TABLE DROP CONSTRAINT. Here’s how you’d normally do it in standard SQL:

SQL

To remove a foreign key in SQLite:

  1. Create a new version of the table without the foreign key.
  2. Copy data into it.
  3. Drop the old table.
  4. Rename the new one.

SQL Foreign Key Labs

Tutorials dojo strip