Foreign keys: linking two tables
45 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A foreign key is a column in one table that must hold a value from the primary key of another table, such as orders.customer_id pointing to customers.customer_id. MySQL then refuses any order for a customer who does not exist, so the link between the two tables can never break.
A foreign key points to a primary key
A foreign key is a column whose values must exist in the primary key of another table, or be NULL. MySQL checks it on every insert and update, so a child row can never point at a parent that does not exist.
Example 1 books an appointment for a doctor who does not exist, and MySQL accepts it because nothing checks the link.
Example 1 · Without a foreign key: an orphan gets in
CREATE TABLE doctors ( doctor_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL, dept VARCHAR(30) NOT NULL);
CREATE TABLE appointments_loose ( appt_id INT AUTO_INCREMENT PRIMARY KEY, patient VARCHAR(40) NOT NULL, doctor_id INT NOT NULL -- just a number, no link);
INSERT INTO doctors (name, dept) VALUES ('Dr. Sen', 'Cardiology'), ('Dr. Iyer', 'Ortho');
INSERT INTO appointments_loose (patient, doctor_id) VALUES ('Kavya', 1);INSERT INTO appointments_loose (patient, doctor_id) VALUES ('Imran', 9); -- no doctor 9
SELECT * FROM appointments_loose ORDER BY appt_id;+---------+---------+-----------+ | appt_id | patient | doctor_id | +---------+---------+-----------+ | 1 | Kavya | 1 | | 2 | Imran | 9 | +---------+---------+-----------+
What this code does
- There are only doctors 1 and 2, yet MySQL stores Imran's appointment with doctor 9, because
doctor_idis an ordinary INT. - Imran's row is an orphan: no screen can show his doctor's name, and he waits for a doctor who does not exist.
The table being pointed to is the parent (customers); the table that holds the pointer is the child (orders). Each order stores only the customer's id, not their name and city again.
A plain INT column is just a number, though. Nothing stops an order from saying customer_id = 99 when there is no customer 99. Such a row is called an orphan: it points at nothing, so reports lose it and the delivery app cannot find the address. Example 1 creates one. The foreign key turns that number into a promise: every value in the child column must exist in the parent's key, or be NULL. The database checks it on every insert and update, in every application.
employees.manager_id points to employees.emp_id, and categories.parent_id points to categories.category_id.REFERENCES in CREATE TABLE and ALTER TABLE
The REFERENCES clause is the part of a foreign key that names the parent table and its key column, as in FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id). MySQL then checks every child value against that column.
One extra line in CREATE TABLE turns the plain number into a checked link. Example 2 repeats the tables with it; Example 3 lists every foreign key of the practice database.
CREATE TABLE wards ( ward_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL);
-- Wrong: BIGINT does not match the parent's INTCREATE TABLE ward_beds ( bed_id INT PRIMARY KEY, ward_id BIGINT NOT NULL, CONSTRAINT fk_ward_beds_ward FOREIGN KEY (ward_id) REFERENCES wards (ward_id));ERROR 3780 (HY000): Referencing column 'ward_id' and referenced column 'ward_id' in foreign key constraint 'fk_ward_beds_ward' are incompatible.
Example 2 · The same tables, linked with a foreign key
CREATE TABLE doctors ( doctor_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL, dept VARCHAR(30) NOT NULL);
CREATE TABLE appointments ( appt_id INT AUTO_INCREMENT PRIMARY KEY, patient VARCHAR(40) NOT NULL, doctor_id INT NOT NULL, CONSTRAINT fk_appointments_doctor FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id));
INSERT INTO doctors (name, dept) VALUES ('Dr. Sen', 'Cardiology'), ('Dr. Iyer', 'Ortho');INSERT INTO appointments (patient, doctor_id) VALUES ('Kavya', 1), ('Rahul', 2), ('Sneha', 1);
DESCRIBE appointments;SELECT * FROM appointments ORDER BY appt_id;+-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | appt_id | int | NO | PRI | NULL | auto_increment | | patient | varchar(40) | NO | | NULL | | | doctor_id | int | NO | MUL | NULL | | +-----------+-------------+------+-----+---------+----------------+ +---------+---------+-----------+ | appt_id | patient | doctor_id | +---------+---------+-----------+ | 1 | Kavya | 1 | | 2 | Rahul | 2 | | 3 | Sneha | 1 | +---------+---------+-----------+
What this code does
CONSTRAINT fk_appointments_doctor FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id)says: everydoctor_idhere must be adoctor_idindoctors.MULin theKeycolumn is the index MySQL added for the foreign key. All three appointments point at real doctors, so they are stored.- Example 4 sends the orphan from Example 1 to a table like this and shows the error.
Example 3 · Every foreign key in the practice database
SELECT table_name, column_name, constraint_name, referenced_table_name, referenced_column_nameFROM information_schema.key_column_usageWHERE table_schema = DATABASE() AND referenced_table_name IS NOT NULLORDER BY table_name, column_name;+-------------+-------------+--------------------+-----------------------+------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +-------------+-------------+--------------------+-----------------------+------------------------+ | categories | parent_id | categories_ibfk_1 | categories | category_id | | employees | dept_id | employees_ibfk_1 | departments | dept_id | | employees | manager_id | employees_ibfk_2 | employees | emp_id | | enrollments | course_id | enrollments_ibfk_2 | courses | course_id | | enrollments | student_id | enrollments_ibfk_1 | students | student_id | | order_items | order_id | order_items_ibfk_1 | orders | order_id | | order_items | product_id | order_items_ibfk_2 | products | product_id | | orders | customer_id | orders_ibfk_1 | customers | customer_id | | products | category_id | products_ibfk_1 | categories | category_id | +-------------+-------------+--------------------+-----------------------+------------------------+
What this code does
information_schema.key_column_usagelists key columns; rows with areferenced_table_nameare foreign keys. Each line reads "child.column points to parent.column".- The practice script did not name its foreign keys, so MySQL called them
orders_ibfk_1,order_items_ibfk_2and so on (ibfk= InnoDB foreign key). order_itemshas two foreign keys, toordersand toproducts;categoriesandemployeespoint to themselves.
Write it after the columns, with a name in front: CONSTRAINT fk_appointments_doctor FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id). Without a name, MySQL calls it appointments_ibfk_1.
To link tables that already exist, use ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent (key_col);. MySQL first checks every existing row and refuses with error 1452 if one of them is an orphan. Three rules must hold. The parent column is a PRIMARY KEY (or UNIQUE NOT NULL). Both columns have the same type: INT with INT, not INT with BIGINT or INT UNSIGNED. And both tables use InnoDB, the default engine; the old MyISAM engine accepts the line and silently ignores it.
DESCRIBE shows MUL in its Key column. That index makes the checks fast.What the database refuses: missing parents
ERROR 1452 is the error MySQL raises when an INSERT or UPDATE puts a value in a child column that does not exist in the parent table. The message names the child table, the constraint and both columns.
ShopLite has customers 1 to 10. Example 4 adds an order for customer 3, which works, and an order for a customer who is not there, which does not.
Example 4 · An order for a customer who does not exist
-- ShopLite has customers 1 to 10INSERT INTO orders (customer_id, order_date) VALUES (3, '2025-06-02'); -- fine
SELECT order_id, customer_id, order_date, statusFROM ordersWHERE order_id = 16;
INSERT INTO orders (customer_id, order_date) VALUES (99, '2025-06-02'); -- refused+----------+-------------+------------+--------+ | order_id | customer_id | order_date | status | +----------+-------------+------------+--------+ | 16 | 3 | 2025-06-02 | PLACED | +----------+-------------+------------+--------+ ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
What this code does
- The first order points at customer 3, who exists, so it gets id 16. Customer 99 does not exist, so MySQL refuses the second insert with error 1452.
- The message names your database (here
practice), the child tableorders, the constraintorders_ibfk_1and both columns. It is "Sample output" only because your database name may differ. - An UPDATE that sets
customer_id = 99on an existing order fails with the same error.
Example 5 · NULL means "not linked yet"
CREATE TABLE doctors ( doctor_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL);
CREATE TABLE walk_ins ( visit_id INT AUTO_INCREMENT PRIMARY KEY, patient VARCHAR(40) NOT NULL, doctor_id INT NULL, -- assigned later CONSTRAINT fk_walk_ins_doctor FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id));
INSERT INTO doctors (name) VALUES ('Dr. Sen');INSERT INTO walk_ins (patient, doctor_id) VALUES ('Arjun', 1);INSERT INTO walk_ins (patient) VALUES ('Divya'); -- no doctor yet
SELECT * FROM walk_ins ORDER BY visit_id;+----------+---------+-----------+ | visit_id | patient | doctor_id | +----------+---------+-----------+ | 1 | Arjun | 1 | | 2 | Divya | NULL | +----------+---------+-----------+
What this code does
- Divya's
doctor_idis NULL, and the foreign key accepts it: NULL is not a value to look up, it means "not linked yet". - The practice table
productsuses the same idea: the gift card has a NULLcategory_id. Write NOT NULL when every child must have a parent.
The opposite direction is protected too. Deleting or changing a parent row that still has children gives ERROR 1451: Cannot delete or update a parent row, and dropping the parent table gives ERROR 3730. The next lesson shows how to choose what happens on delete instead. NULL is not checked. A foreign key column that allows NULL can stay empty, which means "not linked yet": a product with no category, or an appointment with no doctor assigned.
Fix the error
Fix the order
Read the real error, then pick the line that fixes it.
Customer 99 does not exist in ShopLite. Order for an existing customer instead.
Error
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shoplite`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
Insert order: parents before children
Insert order is the rule that parent tables and parent rows come before the children that point to them: categories before products, customers before orders. Removing goes the other way round, children first.
A child needs its parent first, so Example 6 creates the child table before its parent, and MySQL stops with an error.
CREATE TABLE wards ( ward_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL);
CREATE TABLE ward_beds ( bed_id INT PRIMARY KEY, ward_id INT NOT NULL, CONSTRAINT fk_ward_beds_ward FOREIGN KEY (ward_id) REFERENCES wards (ward_id));
DROP TABLE wards; -- the parent first: refusedERROR 3730 (HY000): Cannot drop table 'wards' referenced by a foreign key constraint 'fk_ward_beds_ward' on table 'ward_beds'.
Example 6 · Creating the child table first
-- Wrong order: the child is created before its parentCREATE TABLE match_tickets ( ticket_id INT AUTO_INCREMENT PRIMARY KEY, match_id INT NOT NULL, stand VARCHAR(10) NOT NULL, CONSTRAINT fk_match_tickets_match FOREIGN KEY (match_id) REFERENCES ipl_matches (match_id));
CREATE TABLE ipl_matches ( match_id INT PRIMARY KEY, teams VARCHAR(40) NOT NULL, match_date DATE NOT NULL);ERROR 1824 (HY000): Failed to open the referenced table 'ipl_matches'
What this code does
ipl_matchesdoes not exist yet whenmatch_ticketsis created, so MySQL cannot set up the link and stops with error 1824.- Swap the two statements: create
ipl_matchesfirst. Rows follow the same rule, as practice 4 shows.
For ShopLite the order is: categories, then products; customers, then orders; and orders and products before order_items.
The same rule applies to creating tables: create the parent table before the child that references it, or MySQL stops with ERROR 1824: Failed to open the referenced table. A setup script, such as the course's sample-database.sql, follows this order. That script also starts with SET FOREIGN_KEY_CHECKS = 0 to drop old tables in any order. That switch turns all foreign key checks off for the session, so use it only in reset scripts and switch it back on (= 1) straight after.
Next step
Check your understanding
11 quick questions