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.

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:

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:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

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