This article will discuss how to add foreign keys while creating a table and to existing tables.
Table of Contents:-
Add foreign key while creating a table in MySQL
We will be creating a table named student_details and inserting some rows into it, followed by creating another table student_address_city with a foreign key student_id associated with the student_details‘ primary key.
- Create the table student_details and insert rows to it.
CREATE TABLE student_details ( student_id INT AUTO_INCREMENT, student_name VARCHAR(255), sex VARCHAR(50), age INT, PRIMARY KEY (student_id) ); INSERT INTO student_details (student_name,sex,age) VALUES("Henry","Male",23), ("Henric","Male",24), ("Sandy","Female",25), ("Richa","Female",23), ("Jennifer","Female",23), ("Rosy","Female",26);
2. View the snapshot of student_details by executing:
SELECT * FROM student_details;
Output:-

3. Create table student_address_city with foreign key constraint and insert few rows into it.
CREATE TABLE student_address_city ( address_id INT NOT NULL AUTO_INCREMENT, student_id int NOT NULL, city varchar(255) DEFAULT NULL, PRIMARY KEY (address_id), FOREIGN KEY (student_id) REFERENCES student_details(student_id) ); INSERT INTO student_address_city (student_id,city) VALUES (1,"Santiago"), (2,"Chicago"), (3,"Washington"), (4,"New York"), (5,"Chicago"), (6,"Washington");
4. Table is created, and data added successfully. Verifying the same by executing:
Frequently Asked:
SELECT * FROM student_address_city;

Output in image_2 shows that student_id foreign key has accepted the values 1 to 6. These values are permitted here because student_id values 1 to 6 are also present in its reference table student_details. Let us now try and insert any other student_id value, maybe 20, which is not present in the student_details table.
INSERT INTO student_address_city (student_id,city) VALUES (20,"Chicago");
Action Output Message:-
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (riti_sales_db
.student_address_city
, CONSTRAINT student_address_city_ibfk_1
FOREIGN KEY (student_id
) REFERENCES student_details
(student_id
))
Add foreign key to an existing table in MySQL
In this section, we will discuss how to add a foreign key to an existing table. We will be using ALTER TABLE statement.
- To demonstrate the same, let us drop the table student_address_city and recreate it without foreign key constraint.
DROP TABLE student_address_city; CREATE TABLE student_address_city ( address_id INT NOT NULL AUTO_INCREMENT, student_id int NOT NULL, city varchar(255) DEFAULT NULL, PRIMARY KEY (address_id) );
Action Output:-

The table has been dropped and recreated by the above script.
2. We will now add the foreign key constraint on student_id to the existing table student_address_city.
ALTER TABLE student_address_city ADD CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student_details(student_id);
Action Output:-

The highlighted row in image_5 shows that the foreign key constraint is added.
3. Try to insert two rows, one with student_id present in the student_details table and another one that is not present in student_details.
INSERT INTO student_address_city (student_id,city) VALUES (2,"Chicago"); INSERT INTO student_address_city (student_id,city) VALUES (20,"Chicago");
Action Output:-

Output in image_5 shows that: MySQL server added the first insert statement with student_id = 2 successfully, but executing the second insert statement returned an error: Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (riti_sales_db
.student_address_city
, CONSTRAINT fk_student_id
FOREIGN KEY (student_id
) REFERENCES student_details
(student_id
))
4. Another way to verify if the foreign key constraint got added or not is by getting this information from INFORMATION SCHEMA via executing the below query:
SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'student_address_city';
Action Output:-

READ MORE
We hope this article helped in adding foreign keys to the MYSQL table. Good Luck!!!