1

I am importing a database from an SQL dump and I get this error:

Cannot add or update a child row: a foreign key constraint fails 
(`database_name`.`#sql-808_37`, CONSTRAINT `FK_z_log_zemail` FOREIGN KEY (`ID_evn`)
REFERENCES `z_event` (`ID_evn`) ON DELETE SET NULL)

Operation failed with exitcode 1"

I know that it means that the foreign key in the child table is not in the parent table z_event, hence the error.

Question:

  1. Was the process of importing the sql dump aborted from this line on? (Operation failed with exitcode 1)

  2. Is there any "clean" way of bypassing this error without compromising the data integrity? Or any other solutions that do not involve removing the constraint?

  3. Why does this happens? Some bad relationship settings between tables? Like when something was deleted from the parent table, the child table was not updated?

  4. Is there any chance that the sql dump is corrupted? Or is this error pretty common? I am asking to know if I should be worried or not..

Philip Pittle
  • 11,821
  • 8
  • 59
  • 123
frankmookie
  • 15
  • 6

1 Answers1

2

Answers:

  1. It depends on how are you restoring the database. If you are running a script making inserts it may have skipped the problem and continue, but if you are making a bulk insert (with COPY) it will fail completly.

  2. No way to clean bypass.

  3. Maybe the dump is in wrong order. The table needed is below (or after) the table that references it. In your case you have to ensure that z_event is loaded first.

  4. Maybe it is corrupted, but in my experience the 3rd is the usual explanation.

lalborno
  • 414
  • 3
  • 5
  • I also think that the 3rd is unlikely. Unless the dump was made with the constraints disabled. – Jorge Campos Jul 21 '14 at 20:33
  • Thank you for your reply. In the dump file the constraints are added at the bottom. So my guess is that there were some issues while deleting or updateing records. But do you have any possible solutions? – frankmookie Jul 21 '14 at 20:35
  • If the constraints are added at the bottom it shouldn't fail with the error described. Maybe you already had the constraints before the beginning of the data load. If you do not want to drop the constraints you need to reorder the load script. I would split the script in several files (one for each table) and try one by one. – lalborno Jul 21 '14 at 20:46
  • 1
    But they are at the bottom. The line is almost at the end of the file. It makes sense no? First it inserts in the table and then when activating the constraints I get this errors.. @JorgeCampos I will try it out thks! – frankmookie Jul 21 '14 at 20:49