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.
SQL Foreign Key Syntax
CREATE TABLE table_name (
column_name datatype,
...
FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
);
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.
CREATE TABLE PrescriptionsNew (
prescription_id INTEGER PRIMARY KEY,
patient_id INTEGER,
medication_name TEXT,
FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)
);

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:
- Create a new table with the foreign key.
- Copy data from the old table.
- Drop the old table.
- 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:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
To remove a foreign key in SQLite:
- Create a new version of the table without the foreign key.
- Copy data into it.
- Drop the old table.
- Rename the new one.