MySQL does not support foreign key constraints on partitioned InnoDB tables. You must choose between using partitioning or foreign keys. Remove partitioning or foreign keys, use application-level enforcement, or implement trigger-based validation instead.
This error occurs when you attempt to create a partitioned InnoDB table that contains foreign key constraints, or to partition an existing table that has foreign keys defined. MySQL's partitioning implementation is fundamentally incompatible with InnoDB's foreign key mechanism. The error message "ERROR 1506: Foreign keys are not yet supported in conjunction with partitioning" reflects a core architectural limitation: InnoDB foreign keys require strict referential integrity checks across the entire table, while partitioning distributes data across separate logical partitions. Managing foreign key constraints across partitions would require coordination that InnoDB's current architecture cannot support efficiently. This limitation applies bidirectionally: you cannot add foreign key constraints to a partitioned table, nor can you partition a table that already has foreign keys defined. Additionally, a partitioned table cannot have columns that are referenced by foreign keys in other tables.
Before making changes, evaluate your requirements:
Partitioning is critical if:
- Table has millions or billions of rows
- Need to manage retention policies (archive old partitions)
- Performance benefits from partition pruning are significant
- Query patterns isolate to specific partitions
Foreign keys are critical if:
- Data integrity is paramount
- Many child tables reference this table
- Cascade deletes simplify cleanup logic
- Prevent orphaned records is essential
Choose to keep whichever is more important. In most cases, partitioning (for scalability) takes priority.
If partitioning is more important, drop the foreign key constraints:
-- View existing foreign keys
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'your_table' AND REFERENCED_TABLE_NAME IS NOT NULL;
-- Drop the foreign key
ALTER TABLE your_table DROP FOREIGN KEY constraint_name;
-- Now you can add partitioning
ALTER TABLE your_table PARTITION BY RANGE (year(date_column)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION pmax VALUES LESS THAN MAXVALUE
);Trade-off: You lose automatic referential integrity enforcement. Orphaned records are possible if the application doesn't validate.
If referential integrity is critical, remove partitioning:
-- Check if table is partitioned
SELECT PARTITION_METHOD, PARTITION_EXPRESSION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'your_table' AND TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME;
-- Remove partitioning (rebuilds table as a single partition)
ALTER TABLE your_table REMOVE PARTITIONING;
-- Now foreign keys work normally
ALTER TABLE your_table ADD CONSTRAINT fk_parent_id
FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE;Trade-off: Large tables cannot use partition pruning. Archiving old data requires manual deletion or different strategies.
For partitioned tables, implement referential integrity in your application:
# Python example: validate before insert
def insert_child_record(parent_id, child_data):
# Check parent exists
parent = db.query("SELECT id FROM parent_table WHERE id = %s", parent_id)
if not parent:
raise ForeignKeyViolation(f"Parent ID {parent_id} does not exist")
# Safe to insert
db.query("INSERT INTO child_table (parent_id, ...) VALUES (%s, ...)", parent_id, child_data)Pros: Partitioning and scalability work. Flexibility for complex rules.
Cons: Cannot rely on database-level enforcement. Requires disciplined application code. Audit trails are harder.
Create BEFORE INSERT/UPDATE triggers to validate foreign key relationships:
DELIMITER //
CREATE TRIGGER validate_fk_before_insert
BEFORE INSERT ON child_partitioned_table
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM parent_table WHERE id = NEW.parent_id) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Foreign key violation: parent_id does not exist';
END IF;
END//
CREATE TRIGGER validate_fk_before_update
BEFORE UPDATE ON child_partitioned_table
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM parent_table WHERE id = NEW.parent_id) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Foreign key violation: parent_id does not exist';
END IF;
END//
DELIMITER ;Pros: Keeps both partitioning and validation. Database-enforced constraints.
Cons: Triggers add overhead. Cascade deletes must be manual. Schema changes require trigger updates.
For some use cases, restructuring the schema avoids the problem entirely:
-- Instead of large_partitioned_table with FK to small_table:
-- Keep small_table non-partitioned
-- Copy required columns from small_table into large_partitioned_table
CREATE TABLE large_partitioned_table (
id BIGINT,
-- Copy these from small_table instead of using FK:
small_table_id INT,
small_table_name VARCHAR(255), -- Denormalized
small_table_status VARCHAR(50), -- Denormalized
-- ... partition by date ...
) PARTITION BY RANGE (YEAR(created_at)) ...;
-- Sync denormalized columns when small_table changes (via application or trigger)Pros: Fully supports partitioning. Avoids cross-table lookups.
Cons: Data duplication. Must keep denormalized columns in sync.
PostgreSQL Alternative: If your project needs both foreign keys and partitioning, PostgreSQL (version 11+) fully supports foreign keys on partitioned tables and is worth considering as a migration path.
MySQL HeatWave: MySQL HeatWave (Oracle's in-memory analytics) has some experimental support for foreign keys with partitioned tables, but it's not production-ready in all scenarios.
Performance Impact of Removing Partitions: Removing partitioning from a large table requires a table rebuild that locks writes. Schedule this during maintenance windows:
-- For large tables, this can take hours
ALTER TABLE large_table REMOVE PARTITIONING;
-- Monitor progress with:
SHOW PROCESSLIST;Historical Note: This limitation has existed since MySQL 5.1 (when partitioning was introduced) and remains in MySQL 8.0+. It's not expected to change soon, so permanent workarounds are necessary.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL