# Mysql foreign keys error when trying to partition the table **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [October 21, 2013, 1:06pm UTC](https://forums.percona.com/t/mysql-foreign-keys-error-when-trying-to-partition-the-table/3020 "2013-10-21T13:06:04Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![rreddy](https://avatars.discourse-cdn.com/v4/letter/r/49beb7/32.png) [@rreddy](https://forums.percona.com/u/rreddy)\ **Post date:** [October 21, 2013, 1:06pm UTC](https://forums.percona.com/t/mysql-foreign-keys-error-when-trying-to-partition-the-table/3020/1 "2013-10-21T13:06:04Z")
Hi, I am trying to do few things together in one ALTER statement on a table that is not referenced by any other tables, NO child tables. 1. Drop foreign keys. 2. Drop primary key, add new primary key that also includes the partition key ( id, when\_logged) 3. Range partition the table using when\_logged partition key. Here is the problem, I am unable to do all of these in one shot, mysql gives a strange error which is unrelated to the work I am doing as the table that I am modifying is not referenced by any other table, but the alter fails with this statement. "Cannot delete or update a parent row: a foreign key constraint fails " ALTER TABLE CSR\_AUDIT\_LOG\_TEST DROP FOREIGN KEY CSR\_AUDIT\_LOG\_TEST\_ibfk\_1,DROP FOREIGN KEY CSR\_AUDIT\_LOG\_TEST\_ibfk\_2, DROP FOREIGN KEY CSR\_AUDIT\_LOG\_TEST\_ibfk\_3, DROP FOREIGN KEY CSR\_AUDIT\_LOG\_TEST\_ibfk\_4,DROP PRIMARY KEY, ADD PRIMARY KEY(ID, WHEN\_LOGGED) PARTITION BY RANGE (to\_days(WHEN\_LOGGED)) ( PARTITION p200701 VALUES LESS THAN (to\_days(‘2007-02-01’)) ENGINE = InnoDB, PARTITION p200702 VALUES LESS THAN (to\_days(‘2007-03-01’)) . . ENGINE = InnoDBPARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB); But when I breakdown my ALTER into two statements, it runs fine, so I do all my DROP foreign key and primary key related changes in one go and partitioning in one go , I can make these changes. Not sure why my initial ALTER statement fails with unrelated error message. --------------------------- Info ------------------------------------------------ Table: CSR\_AUDIT\_LOG\_TEST Create Table: CREATE TABLE `CSR_AUDIT_LOG_TEST` ( `ID` int(10) unsigned NOT NULL AUTO\_INCREMENT, `CZEN_USER_ID` int(10) unsigned DEFAULT NULL, `MEMBER_ID` int(10) unsigned DEFAULT NULL, `ACTION` varchar(50) NOT NULL, `WHEN_LOGGED` datetime NOT NULL, `BUCKET_TYPE` enum(‘GENERAL\_INFO’,‘SERVICE\_PROFILE’,‘REFERENCE’, ‘PHOTO’,‘RECORDED\_REFERENCE’,‘REVIEW’,‘VIDEO’,‘JOB ‘,‘CARE\_ON\_CALL\_JOB’,‘EXCHANGE\_AD’,‘BACKGROUND\_CHE CK’,‘BASIC\_BACKGROUND\_CHECK’,‘EMPLOYER\_JOB’,‘RESUM E’,‘CARE\_GROUP’,‘LEAD\_GEN\_PARTNER\_LOCATION’,‘LEAD\_ GEN\_PARTNER\_LOCATION\_PROFILE’,‘LOCATION\_PROFILE\_IM AGE’,‘LOCATION\_PROFILE\_SPECIAL\_OFFER’,‘LOCATION\_PR OFILE\_MULTIPLE\_PHOTO’,‘RECRUITMENT\_PROFILE’,‘RECRU ITMENT\_JOB’,‘WRITTEN\_REFERENCE’,‘MEMBER\_HIRE’) NOT NULL, `BUCKET_ID` int(10) unsigned DEFAULT NULL COMMENT ‘id of the bucket’, `APPROVAL_STATUS` enum(‘pending’,‘pendAudit’,‘reviewed’,‘approved’,’ rejected’,‘clearedForSafety’) DEFAULT NULL, `SERVICE_ID` varchar(9) DEFAULT NULL, `REASON_CODE` varchar(20) DEFAULT NULL, `BUCKET_CHANGE_STATUS` varchar(20) DEFAULT NULL COMMENT ‘the status of the bucket, can be NEW or ACTIVE’, `BUCKET_CHANGE_XML` varchar(10000) DEFAULT NULL, `MEMBER_CHANGE_STATUS` varchar(20) DEFAULT NULL COMMENT ‘the status of the member, can be NEW or ACTIVE’, `BUCKET_REVIEW_TIME` int(10) unsigned DEFAULT NULL COMMENT ‘Stores the time taken to review the bucket by CSR in milliseconds’, `CZEN_USER_SHIFT_ID` int(10) unsigned DEFAULT NULL COMMENT ‘CSR Shift in which this log got created’, PRIMARY KEY (`ID`), KEY `CZEN_USER_ID` (`CZEN_USER_ID`), KEY `MEMBER_ID` (`MEMBER_ID`), KEY `SERVICE_ID` (`SERVICE_ID`), KEY `CZEN_USER_SHIFT_ID` (`CZEN_USER_SHIFT_ID`), KEY `IX_WHEN_LOGGED` (`WHEN_LOGGED`), CONSTRAINT `CSR_AUDIT_LOG_TEST_ibfk_1` FOREIGN KEY (`CZEN_USER_ID`) REFERENCES `CZEN_USER` (`ID`), CONSTRAINT `CSR_AUDIT_LOG_TEST_ibfk_2` FOREIGN KEY (`MEMBER_ID`) REFERENCES `MEMBER` (`ID`), CONSTRAINT `CSR_AUDIT_LOG_TEST_ibfk_3` FOREIGN KEY (`SERVICE_ID`) REFERENCES `SERVICE` (`ID`), CONSTRAINT `CSR_AUDIT_LOG_TEST_ibfk_4` FOREIGN KEY (`CZEN_USER_SHIFT_ID`) REFERENCES `CZEN_USER_SHIFT` (`ID`) ) ENGINE=InnoDB AUTO\_INCREMENT=117808378 DEFAULT CHARSET=latin1 COMMENT=‘Audit log for changes made by a CSR to a member account’ 1 row in set (0.00 sec) ---
**Author:** ![niljoshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/niljoshi/32/16889_2.png) [@niljoshi](https://forums.percona.com/u/niljoshi)\ **Post date:** [October 22, 2013, 10:39pm UTC](https://forums.percona.com/t/mysql-foreign-keys-error-when-trying-to-partition-the-table/3020/2 "2013-10-22T22:39:24Z")
Hi, Can you tell me which MySQL/PS version you are using? Can you also provide “SHOW ENGINE INNODB STATUS” while you are getting that error? Because you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS. error.log will be also helpful to troubleshoot the issue.