How to add a foreign key to an existing table in MySQL




Syntax for adding a FOREIGN KEY to an existing MySQL table:

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;

For example, we have table students and we need to add a FOREIGN KEY to the column student_id, referencing the id column from the table points.

First, make sure the column student_id is already added in the table students. Otherwise, create a new column student_id, because you can’t add a FOREIGN KEY unless the column exists.

ALTER TABLE students ADD COLUMN student_id INT NOT NULL;

Assign a FOREIGN KEY to the newly created column

ALTER TABLE students ADD FOREIGN KEY (student_id) REFERENCES points(id);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE students ADD CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES points(id);

---

Need a good MySQL GUI? TablePlus provides a native client that allows you to access and manage MySQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS