MySQL ADD FOREIGN KEY

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
• Add foreign key to an existing table in MySQL

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.

1. 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:

 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.

1. 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

• MySQL ADD COLUMN IF NOT EXISTS
• MYSQL: CREATE A DATABASE

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