EC
1740
MariaDBERRORNotablePartitioningHIGH confidence

Cannot use EXCHANGE PARTITION when the table has foreign key constraints

Production Risk

Medium — operation blocked; no data moved until FK is removed.

What this means

ALTER TABLE ... EXCHANGE PARTITION cannot be performed when either the partitioned table or the exchange table has foreign key constraints defined.

Why it happens
  1. 1The partitioned table has a FOREIGN KEY constraint.
  2. 2The exchange (staging) table has a FOREIGN KEY constraint.
How to reproduce
trigger — this will error
trigger — this will error
-- orders has a FK on customer_id referencing customers.
ALTER TABLE orders EXCHANGE PARTITION p0 WITH TABLE staging;

expected output

ERROR 1740 (HY000): Table 'orders' has a foreign key constraint. ALTER TABLE ... EXCHANGE PARTITION is not supported for tables with foreign key constraints.

Fix

Drop the foreign key constraint before exchanging, then recreate it

Drop the foreign key constraint before exchanging, then recreate it
ALTER TABLE orders DROP FOREIGN KEY fk_customer;
ALTER TABLE orders EXCHANGE PARTITION p0 WITH TABLE staging;
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

Why this works

Temporarily removing the FK allows the exchange to proceed; it is then restored afterwards.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1740 ER_PARTITION_EXCHANGE_FOREIGN_KEY

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors