0

I am trying to restore a DB using an SQL script, but things foreign key constraints get in the way

I am taking a MySQL DB and bringing it over to PostgreSQL. Since the MySQL create table syntax ended up being quite different, I took another PostgreSQL DB with the same schema, but different data and restored the schema only, from that. In other words, I now have a database with tables, constraints, sequences and all that shnaz but no data inside.

So, it's is time to restore data. I take a backup of the MySQL DB with phpMyAdmin (data only) as an SQL script (pgAdmin does not seem to accept zip or gzip files for some reason) and run the SQL script. Now, this is where the problems start to happen, it's only natural, I am going from MySQL to PostgreSQL, so syntax errors are bound to happen.

But, there are other non syntax related problems to, like this one:

ERROR: insert or update on table "_account" violates foreign key constraint "fk_1_account"
DETAIL:  Key (accountid)=(2) is not present in table "_entity".

So, yeah, basically, a foreign constraint exists, the query is trying to insert data into the _account table, but the corresponding data has not been inserted into the _entity table yet. How do I get around that? Is there a way to make pgAdmin3/PostgreSQL disable ALL OF the constraints, insert the data, and then re-enable the constraints?

A syntax related error I encountered, was this one:

 INSERT INTO _accounttype_seq (id) VALUES (11);

The PostgreSQL equivalent of that statement (if I am correct) is

 ALTER SEQUENCE _accounttype_seq INCREMENT BY 11;

But, it's a bit of a pain to run through the whole script and change all 200+ Sequence insert statements. So, I am being lazy here, but is there an easier way to deal with the sequences as well?

Or, do you guys have any suggestions for a different set of tools to make this easier?

Thanks for your time, have a good day.

Improve this question
2
  • mysql has a foreign_key_checks setting you can disable for the purposes of imports - without disabling them it'd be impossible to import any tables with circular references, or the tables listed in VERY specific orders. PG should have something similar. Commented Nov 1, 2011 at 17:07
  • These might help: 1. stackoverflow.com/questions/2679854/…, 2. postgresql.org/docs/8.2/static/sql-set-constraints.html Commented Nov 1, 2011 at 17:30

2 Answers 2

Reset to default
2

Do not try to get around the foreign key constraints. That is the way to make sure the data is bad.

First look at the constraints and make sure you are inserting to the tables in the correct order. If _entity is parent of "_account, then it should be populated first.

Next you need to have the script move any failing records to an exception table. Then you can look at them and see what the data integrity issues is and if you need to throw the records away permanently or try to figure out what the missing parent value should be. If it is critical data such as orders where the customer no longer exists (possible in any system that didn't have correct fks to begin with) and you must keep the record and cannot determine what the parent value should have been, you can create an 'Unknown" record in the customer table and assign all bad orders to that customer id.

And manually changing the alter sequences shouldn't take long even if it is boring. There wil be plently of other things you need to handle manually in a conversion of this type.

I would try to find a data import tool for PostgreSQL - I live in SQL server world where I would use SSIS but you need the equivalent of SSIS for the PostgreSQL world.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Haha thanks, yeah in the end, I just moved tables around so they got populated in order.
2

Aparently the foreign keys weren't actually enforced in MySQL (maybe because of using MyISAM) or the generated SQL just does it in the wrong order.

If it's "only" the wrong order, I see two possible solutions:

  1. edit the generated script and either move all FK definitions to the end of the script
  2. Edit the definition of each FK constraint and set them all to initially deferred. Then run the script as one single transaction with only on commit at the very end.

Edit (because this is too much to be put as a comment)

Using SET CONSTRAINTS ALL DEFERRED will only work if the constraints have been created with the option DEFERRABLE.

To run everything in one single transaction, you have to make sure you have turned autocommit off. Then simply run the INSERTs and at the very end issue a COMMIT. A ; will only commit if you have autocommit on.

If you want to be independent of the autocommit setting, then start your script with [BEGIN][1] and make sure there is only a single COMMIT at the very end.

BEGIN DEFERRABLE
   INSERT INTO table_one ... ;
   INSERT INTO table_two ... ; 
   .....
COMMIT;
Improve this answer

2 Comments

The constraint definitions are not part of the query I am running. The tables and constraints are already created. As per Abhay's suggestion, I tried to run the query with this line added at the beginning: SET CONSTRAINTS ALL DEFERRED; but it doesn't work, I think it's because there are many commits. Am I correct in assuming that everytime you end a query with a ;, then the query is committed? If yes, how can I only commit at the end? Thanks for the reply.
Thanks a lot for the help, but unfortunately, I don't think the constraints are created with the deferrable option, so, I just went ahead and shifted around the insert order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.