1513
MySQLERRORNotablePartitioningHIGH confidence
Foreign key constraint is not supported for partitioned tables
Production Risk
Medium — schema design must be changed if using pre-8.0 MySQL.
What this means
ER_FOREIGN_KEY_ON_PARTITIONED (1513, SQLSTATE HY000) is raised when a foreign key constraint is added to or from a partitioned table in MySQL versions prior to 8.0.
Why it happens
- 1CREATE TABLE or ALTER TABLE with a FOREIGN KEY on a partitioned InnoDB table (pre-MySQL 8.0)
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE child (id INT, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent(id) ) PARTITION BY HASH(id) PARTITIONS 4;
expected output
ERROR 1513 (HY000): Foreign key constraint is not supported for partitioned tables
Fix
Remove partitioning or foreign keys (pre-8.0), or upgrade to MySQL 8.0+
Remove partitioning or foreign keys (pre-8.0), or upgrade to MySQL 8.0+
-- MySQL 8.0+ supports foreign keys on partitioned InnoDB tables -- For pre-8.0, enforce referential integrity at the application level -- Or remove partitioning: CREATE TABLE child (id INT, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent(id) );
Why this works
MySQL 8.0 lifted the restriction on foreign keys for InnoDB partitioned tables.
Version notes
MySQL 8.0
Foreign keys on InnoDB partitioned tables are supported from MySQL 8.0.
Sources
Official documentation ↗
MySQL 8.0 — 1513 ER_FOREIGN_KEY_ON_PARTITIONED
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev