How to fix ERROR 1506: Foreign keys are not yet supported in conjunction with partitioning in MySQL

-- 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
);

-- 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;

# 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)

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 ;

-- 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)