FOREIGN KEY

SQL FOREIGN KEY Keyword

The FOREIGN KEY constraint is used to establish a relationship between two tables. It ensures that the value in one column matches a value in a referenced table’s primary key column.

SQL FOREIGN KEY Syntax

CREATE TABLE table_name (
    column_name datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES other_table (column_name)
);

CREATE TABLE table_name (

    column_name datatype,

    ...

    FOREIGN KEY (column_name) REFERENCES other_table (column_name)

);

SQL FOREIGN KEY on CREATE TABLE Example

This example creates a new table named Orders and adds a foreign key referencing the Patients table.

CREATE TABLE Orders (
    order_id INTEGER PRIMARY KEY,
    patient_id INTEGER,
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)
);

CREATE TABLE Orders (

    order_id INTEGER PRIMARY KEY,

    patient_id INTEGER,

    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)

);

Note: This syntax is not supported in SQLite in our TechKubo playground. Foreign key constraints must be enabled explicitly, and adding them requires full DDL support that is limited in the playground.

SQL FOREIGN KEY on ALTER TABLE Example

You can also add a foreign key to an existing table using ALTER TABLE.

ALTER TABLE Prescriptions
ADD FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id);

ALTER TABLE Prescriptions

ADD FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id);

Note: This command will not work in SQLite in our TechKubo playground. SQLite does not allow adding foreign keys to existing tables using ALTER TABLE.

SQL FOREIGN KEY with Named Constraint Example

CREATE TABLE Orders (
    order_id INTEGER PRIMARY KEY,
    patient_id INTEGER,
    CONSTRAINT fk_patient_order FOREIGN KEY (patient_id)
    REFERENCES Patients(patient_id)
);

CREATE TABLE Orders (

    order_id INTEGER PRIMARY KEY,

    patient_id INTEGER,

    CONSTRAINT fk_patient_order FOREIGN KEY (patient_id)

    REFERENCES Patients(patient_id)

);

Note: Named foreign key constraints are not supported in SQLite in our TechKubo playground.

SQL FOREIGN KEY Labs