WS WhereIsStuff
Tutorial Guides
Log In
Sign Up

Advertisement

Google Ad Slot: content-top

MySQL FOREIGN KEY

~1 min read · MYSQL
On this page
❮ Previous Next ❯

MySQL FOREIGN KEY Constraint

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 ❯