I come across two constraint issues all the time (
UNIQUE, and referential integrity constraints) within InnoDB. My question is when these issues arise reproducibly what is typically the solution? I'm asking this from a web application attempting to do transactions on the database; so there isn't much control on what is being done unless I risk taking down the database; so conceptually the solutions I have found are below, and am wondering if input can be provided.
UNIQUE constraint possible solutions:
- Remove 'old' value so new one can be inserted.
ERROR 1062 (23000): Duplicate entry '0' for key 1
FOREIGN KEY constraint failure possible solutions:
- Insert expected value into parent.
foreign_key_checksto false so the transaction doesn't get aborted. I don't like this solution but it works.
SQLSTATE: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails...
I've also thought that maybe there are inconsistencies between tables that causes generated queries to fail; this makes me wonder if
null operations on all the tables could resolve the issue? e.g.
ALTER TABLE <tbl_name> ENGINE=InnoDB;
Suggestions where to also dump the table and re-import it; but this just sounds too much of a hassle for a 'non-guaranteed' fix.
So, I ask what are typical solutions for the above errors?