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.
- Drop foreign keys.
- Drop primary key, add new primary key that also includes the partition key ( id, when_logged)
- 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)