|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
July 2nd, 2009, 04:56 PM
|
|||
|
|||
|
Importing data into tables with referential integrity (causing errors)
Hi,
I wonder if anyone can point me in the direction of a solution for this, if there is one. I have an import script for a number of tables in my database, the problem is that when I try to run them I get contraint errors. like so Code:
Script line: 6 Cannot add or update a child row: a foreign key constraint fails (`football/jforum_groups`, CONSTRAINT `FK80BB241C6B459AD9` FOREIGN KEY (`parent_id`) REFERENCES `jforum_groups` (`group_id`)) Script line: 12 Cannot add or update a child row: a foreign key constraint fails (`football/jforum_roles`, CONSTRAINT `FK1D86DB75E0D60E4` FOREIGN KEY (`group_id`) REFERENCES `jforum_groups` (`group_id`)) Script line: 30 Duplicate entry '1' for key 1 Script line: 35 Cannot add or update a child row: a foreign key constraint fails (`football/jforum_user_groups`, CONSTRAINT `FKDF950380F824D10` FOREIGN KEY (`user_id`) REFERENCES `jforum_users` (`user_id`)) I thought that if i switched autocommit off, started a new transaction and then called commit at the end then this would solve the problem bus sadly not so. Is there another way in which I can get the data into the tables before the errors are thrown? Thanks for any help in advance.
|
|
#2
July 2nd, 2009, 09:00 PM
|
||||
|
||||
|
Quote:
you have to define them in a particular order, and also load them in that particular order that order is simply this: for every PK/FK relationship, the table with the FK must come after the table with the PK alternatively, if you can re-script them, look for an option which generates the FK relationships as ALTER TABLE statements, and save them for the after all the tables have been created and loaded
|
|
#3
July 3rd, 2009, 02:01 AM
|
|||
|
|||
|
I am not sure if it helps, but if you run the command
Code:
set foreign_key_checks=0; before the import, the foreign key constraints will be simply ignored during the import. Of course if you use it, you must be sure that the foreign key constraints would not be violated after the import is finished. Personally I use it e.g. when I need to move the whole database to another server when the FILE privilege is absent (so I can not use LOAD DATA INFILE and need to fill in the database with a script). Edit: Importing data in some particular order as r937 said is a more correct approach I think. I use set foreign_key_checks=0; only when I am absolutely sure the foreign key constraints could not get violated after the import is finished.
__________________
PHP Programmer Last edited by SKDevelopment : July 3rd, 2009 at 02:10 AM.
|
|
| Viewing: Dev Shed Forums > Databases > MySQL Help > Importing data into tables with referential integrity (causing errors) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
