User Tools

Site Tools


faq:technical:restore_db_constraints

Restore Database Constraints

When restoring a database backup it happens to appear that the constraints are not set (mostly under Windows), because of an error during the restore process. This can cause an inconsistency in the database, when deleting anything in GECAMed.

To restore the constraints in the database you can use this SQL-file, which contains all constraints of the GECAMed version 1.2.00.

As there might be changes to other GECAMed versions (concerning the constraints), we advise you NOT to use this script with other versions than the version 1.2.00.

You can create an SQL-file containing the constraints for your GECAMed version by doing the following:

  1. Install GECAMed on any machine, where no GECAMed is running yet. Use the same GECAMed version as the one you need the constraints for. if you don't have the installer anymore, you can download it from here.
  2. Make a backup of the GECAMed database (i.e. by using PGAdmin III). Use the format “plain” for the backup.
  3. Open the backup with an text editor.
  4. Extract the statements that are like this “ALTER TABLE ONLY «TABLE» ADD CONSTRAINT «CONSTRAINT_NAME» …” and the “SET search_path = …” statements before those alter-statements into another file.

Error handling

When inserting the backup there might occur some error:

ERROR: relation "TABLE" does not exist

In that case you either have not extracted the related set-search-path statement or it wasn't executed before. You can either add it before the alter-statement (it looks like this: “SET search_path = «schema», pg_catalog;”) or directly add the schema before the table (it looks like this: “ALTER TABLE ONLY «schema».«table name» ADD CONSTRAINT …” - if the table name occurs once more in the statement you need to add the schema there as well).

ERROR: multiple primary keys for table "TABLE" are not allowed

This constraint already exists. You can skip this statement.

ERROR: constraint "CONSTRAINT" for relation "TABLE" already exists

This constraint already exists, You can skip this statement.

ERROR: insert or update on table "TABLE" violates foreign key constraint "CONSTRAINT"

There is already an inconsistency in your database. You need to correct this first, before you can add this constraint.

faq/technical/restore_db_constraints.txt · Last modified: 2014/12/18 11:46 (external edit)