MySQL Foreign Key: Definition, Syntax, and Examples

A FOREIGN KEY in MySQL is a constraint used to link records between two tables. It enforces a relationship by ensuring the values in a column (or a set of columns) match the values in a column from another table, usually the Primary Key of the referenced table.

Key Characteristics of a FOREIGN KEY:

Establishes a parent-child relationship between tables.
Maintains referential integrity.
Prevents invalid data by restricting actions that break the relationship.
Supports cascading updates or deletes using ON UPDATE and ON DELETE.

Syntax for Creating FOREIGN KEY

CREATE TABLE child_table (
  column1 data_type,
  column2 data_type,
  FOREIGN KEY (column_name) REFERENCES parent_table(primary_key_column)
);

With Constraint Name (Optional)

CREATE TABLE child_table (
  column1 data_type,
  column2 data_type,
  CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table(primary_key_column)
);

FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "course_id" column when the "courses" table is created:

Example

CREATE TABLE students (

student_id INT PRIMARY KEY,

student_name VARCHAR(50) NOT NULL,

age INT,

course_id INT,

FOREIGN KEY (course_id) REFERENCES courses(course_id)

);

FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint on the "couser_id" column when the "courses" table is already created, use the following SQL:

Example

ALTER TABLE students

ADD CONSTRAINT fk_course

FOREIGN KEY (course_id) REFERENCES courses(course_id);

Explanation:

fk_course → This is the name of the foreign key constraint.
course_id → Column in the students table.
courses(course_id) → Column in the courses table, which is a Primary Key.

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

Example

ALTER TABLE students

DROP FOREIGN KEY fk_course;

Explanation:

DROP FOREIGN KEY fk_course removes the foreign key relationship.
The students table will no longer have any restrictions on the course_id values.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.