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.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
