Troubleshooting·MySQL

MySQL Foreign Keys and ALTER TABLE

A foreign key refuses in both directions - an order for a customer who does not exist, and deleting a customer who still has orders. Then `ALTER TABLE` adds a column to a live table, and `information_schema` shows what engine is enforcing all of it.

Schemas and Data Guide 10 of 45 Intermediate

OSUbuntu 26.04 LTS (resolute)
MySQL8.4.10-0ubuntu0.26.04.1
EngineInnoDB (default)
TimeAbout 16 min
Reviewed27 August 2026

Written against the versions above. Foreign keys are enforced by InnoDB. MyISAM accepts the syntax and ignores the constraint entirely, which is one of several reasons InnoDB is the default.

Every command on this page ran on db-a01.
Server Name	IP Address	OS	Roles	CPU	RAM	HDD
db-a01	192.168.0.81	Ubuntu 26.04 LTS	Primary / Source / Replica Set Member 1	2 Core	4 GB	50 GB

Before you start

The customers and orders tables from the previous guide.

Insert a child row whose parent does not exist
An order for customer 999. There is no customer 999.

ERROR 1452, and the message is unusually generous: it names the database, the table, the constraint by name, the column and what it references. This is why naming constraints - CONSTRAINT fk_orders_customer - is worth the extra words at create time. Unnamed, MySQL generates something like orders_ibfk_1 and the error tells you far less.

Without the foreign key this insert would have succeeded and produced an orphaned order that no join would ever return.
bash Example session
```
$sudo mysql appdb -e "INSERT INTO orders (customer_id, total) VALUES (999, 5.00)" ; echo "exit=$?"ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (`appdb`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))exit=1
```
Expected resultERROR 1452 (23000): Cannot add or update a child row naming fk_orders_customer and exit=1.

Success conditionYou have seen the constraint refuse an orphan.
Delete a parent row that still has children
The same constraint, enforced from the other side. Ada has two orders.

ERROR 1451 - a different number for the mirror-image problem, which is worth noticing: 1452 is "the parent does not exist", 1451 is "the child still does".

The default behaviour is RESTRICT: refuse. The alternatives are declared on the constraint - ON DELETE CASCADE deletes the orders with the customer, ON DELETE SET NULL keeps them and blanks the reference. All three are defensible; the important thing is that it is a decision, and RESTRICT is the one that makes you take it.
bash Example session
```
$sudo mysql appdb -e "DELETE FROM customers WHERE id = 1" ; echo "exit=$?"ERROR 1451 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails (`appdb`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))exit=1
```
Expected resultERROR 1451 (23000): Cannot delete or update a parent row and exit=1.

Success conditionYou know why a delete can fail on a table you have full rights to.

Add a column to a table that already has data

ALTER TABLE ... ADD COLUMN with NOT NULL DEFAULT 'active'. Existing rows need a value, and the default is what supplies it - adding a NOT NULL column with no default to a populated table fails.

AFTER email places it deliberately rather than at the end. Column order has no effect on storage in InnoDB, but it affects every SELECT * and every DESCRIBE a human reads.

ENUM is a reasonable fit for a short fixed set: it stores as an integer internally and rejects anything not in the list. It is a poor fit for a set that changes, because adding a value is another ALTER TABLE.

bash Example session

$sudo mysql appdb -e "ALTER TABLE customers ADD COLUMN status ENUM('active','suspended') NOT NULL DEFAULT 'active' AFTER email"$sudo mysql --table appdb -e "DESCRIBE customers"+------------+----------------------------+------+-----+-------------------+-------------------+| Field      | Type                       | Null | Key | Default           | Extra             |+------------+----------------------------+------+-----+-------------------+-------------------+| id         | int unsigned               | NO   | PRI | NULL              | auto_increment    || name       | varchar(80)                | NO   |     | NULL              |                   || email      | varchar(120)               | NO   | UNI | NULL              |                   || status     | enum('active','suspended') | NO   |     | active            |                   || credit     | decimal(10,2)              | NO   |     | 0.00              |                   || created_at | timestamp                  | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |+------------+----------------------------+------+-----+-------------------+-------------------+

Expected resultSix columns now, with status between email and credit, defaulting to active.

Success conditionYou have altered a populated table without losing a row.

Confirm which engine is doing the work

Two views. SHOW ENGINES lists what the server supports and marks the default; information_schema.tables reports what each table actually uses.

Both tables are InnoDB, which is what makes everything in this guide possible: foreign keys, transactions and row-level locking are InnoDB features, not MySQL features. The same CREATE TABLE under MyISAM parses the foreign key clause and silently ignores it - no error, no constraint, and the orphaned order from step one would have been written.

The collation column also confirms utf8mb4 reached the tables from the database created three guides ago.

bash Example session

$sudo mysql --table -e "SHOW ENGINES"+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         || MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         || FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       || ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       || MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+$sudo mysql --table appdb -e "SELECT table_name, engine, table_collation FROM information_schema.tables WHERE table_schema='appdb'"+------------+--------+--------------------+| TABLE_NAME | ENGINE | TABLE_COLLATION    |+------------+--------+--------------------+| customers  | InnoDB | utf8mb4_0900_ai_ci || orders     | InnoDB | utf8mb4_0900_ai_ci |+------------+--------+--------------------+

Expected resultInnoDB marked DEFAULT, and both tables reported as InnoDB with utf8mb4 collation.

Success conditionYou can confirm the engine enforcing your constraints.

Troubleshooting

ERROR 1452 when the parent row is definitely there.

Why: A type mismatch between the columns - INT against INT UNSIGNED, or different collations on character keys.

Fix:SHOW CREATE TABLE on both and compare the column definitions exactly, sign included.
ERROR 1451 blocking a cleanup you intend.

Why: RESTRICT is the default and children still reference the row.

Fix:Delete the children first, or declare ON DELETE CASCADE if that is genuinely the behaviour you want. Do not disable foreign_key_checks on a live system - it lets you create the orphans the constraint exists to prevent.
A foreign key clause was accepted but nothing is enforced.

Why: The table is MyISAM. It parses the syntax and ignores it.

Fix:SELECT table_name, engine FROM information_schema.tables WHERE table_schema = DATABASE(), then ALTER TABLE ... ENGINE=InnoDB.

Official sources

← All MySQL guides