MySQL error when targetrule indexer is executed

Question

I'm trying to run the targetrule indexer in my terminal and it shows some syntax error

Command:

php indexer.php --reindex targetrule

Error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`giftease`.`enterprise_targetrule_index_crosssell_product`, CONSTRAINT `FK_94AAB744E142C77E39E72506AB13F090` FOREIGN KEY (`targetrule_id`) REFERENCES `giftease`.`enterprise_targetrule_index_cr)' in /var/www/html/giftease-upgrade/lib/Zend/Db/Statement/Pdo.php:243

I've tried this:

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE enterprise_targetrule_index_crosssell_product;
SET FOREIGN_KEY_CHECKS=1;

With no success

Answer 1

It's better to

1. Remove existing constraints
2. Perform TRUNCATE operation(s)
3. Manually delete rows that now have references to "nowhere", i.e. non-existent relations
4. Re-create constraints

rather than disabling foreign key checks as you might run into data integrity issues.