FOREIGN KEY in SQL

A FOREIGN KEY is a column or group of columns that references a key in another table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

Here, course_id in the students table references course_id in the courses table.

A foreign key helps maintain valid relationships between related tables.

• Connects related tables
• Maintains referential integrity
• Helps prevent invalid references
• Represents relationships between data
• Supports relational database design

The basic table-level syntax is:

FOREIGN KEY (column_name)
REFERENCES parent_table(parent_column)

Example:

FOREIGN KEY (course_id)
REFERENCES courses(course_id)

The table containing the referenced key is commonly called the parent table.

The table containing the foreign key is commonly called the child table.

courses
--------
course_id
course_name

students
--------
student_id
course_id

Here, courses is the parent table and students is the child table.

First, create the table that contains the referenced key.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

The course_id is the primary key of the courses table.

Now create a table that references the parent table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

The students table references the courses table.

A foreign key can be defined while creating a table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

This creates the relationship immediately.

The referenced value should exist in the parent table when the foreign key requires a matching parent.

INSERT INTO courses (course_id, course_name)
VALUES (1, 'Python');

INSERT INTO students (student_id, name, course_id)
VALUES (101, 'Rahul', 1);

Because course_id 1 exists in courses, the student can reference that course.

If a student references a course ID that does not exist, the database may reject the insert.

INSERT INTO students (student_id, name, course_id)
VALUES (102, 'Amit', 99);

If course_id 99 does not exist in courses, the foreign key constraint can prevent the operation.

Foreign key columns are often used when joining related tables.

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.course_id;

This displays student names with their course names.

You can give a foreign key constraint a specific name.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    CONSTRAINT fk_student_course
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

The foreign key constraint is named fk_student_course.

You can add a foreign key to an existing table.

ALTER TABLE students
ADD CONSTRAINT fk_student_course
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

The relationship is added after the table has already been created.

A database can contain many related tables.

students
courses
teachers
departments

Foreign keys can be used to connect these tables according to the database design.

A foreign key can contain multiple columns when it references a composite key.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

A related table can reference the appropriate combination of columns.

A composite foreign key contains more than one column.

FOREIGN KEY (student_id, course_id)
REFERENCES enrollments(student_id, course_id)

The number and order of referenced columns must correspond to the referenced key.

A foreign key column can generally contain NULL when the column allows NULL and no other constraint prevents it.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

A NULL course_id can represent a student who currently has no course assigned, depending on the application design.

You can combine FOREIGN KEY with NOT NULL when every child record must have a related parent.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT NOT NULL,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

Now course_id cannot be NULL.

ON DELETE CASCADE can automatically delete related child records when the referenced parent record is deleted.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
    ON DELETE CASCADE
);

Deleting a referenced course can then delete its related student records according to this rule.

ON UPDATE CASCADE can automatically update matching foreign key values when the referenced key changes.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
    ON UPDATE CASCADE
);

This keeps related key values synchronized according to the constraint.

ON DELETE SET NULL can set the foreign key value to NULL when the referenced parent record is deleted.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
    ON DELETE SET NULL
);

The course_id column must allow NULL for this behavior.

ON DELETE RESTRICT can prevent deletion of a parent record when related child records exist.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
    ON DELETE RESTRICT
);

The exact default behavior can vary by database system.

You can remove a foreign key constraint using ALTER TABLE.

ALTER TABLE students
DROP FOREIGN KEY fk_student_course;

In MySQL, the named foreign key constraint is removed.

Deleting a parent record can be restricted when child records reference it.

DELETE FROM courses
WHERE course_id = 1;

If students still reference course_id 1, the database may reject the deletion unless an appropriate ON DELETE action is configured.

Updating a referenced key can also be affected by foreign key rules.

UPDATE courses
SET course_id = 10
WHERE course_id = 1;

The operation may be restricted or cascaded depending on the foreign key configuration.

PRIMARY KEY and FOREIGN KEY have different purposes.

• PRIMARY KEY uniquely identifies records in its own table.
• FOREIGN KEY references a key in another table.
• A table can have one primary key constraint.
• A table can have multiple foreign key constraints.

A common mistake is trying to create a foreign key when the referenced column does not have a suitable key or the column definitions are incompatible.

FOREIGN KEY (course_id)
REFERENCES courses(course_id)

Before creating the relationship, check that the referenced column is appropriately indexed or keyed and that the column definitions are compatible.

In MySQL, you can inspect the table definition using SHOW CREATE TABLE.

SHOW CREATE TABLE students;

This can help you check foreign key definitions and other constraints.

Suppose an academy has courses and students.

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

The course_id in students connects each student with a course.

In a student management system, a student can be connected to a course using a foreign key.

INSERT INTO courses (course_name)
VALUES ('Python Full Stack');

INSERT INTO students (name, course_id)
VALUES ('Rahul', 1);

The student references the course whose course_id is 1.

Here is a complete example using PRIMARY KEY and FOREIGN KEY together.

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

INSERT INTO courses (course_name)
VALUES ('Python'), ('SQL');

INSERT INTO students (name, email, course_id)
VALUES ('Rahul', 'rahul@example.com', 1);

Here, courses is the parent table and students is the child table. The course_id column creates the relationship between them.