WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

MySQL FOREIGN KEY

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