Post reply

Appending data having trouble with foreign keys

  • August 27, 2014 at 11:58 am

    #290165

    Someone I work with created the wrong ODBC connection and entered data in the wrong database (old copy on an old server that's about to be decommissioned) and I need to move the data to the new location. Transferring most of the data will be a breeze but there's two tables that are causing me problems because one of the table is linked to the other using a foreign key. I'm concerned about maintaining the relational integrity of the tables and updating the foreign key in the child table based on the new primary key in the parent table. Could anyone give me some advice on how to proceed?

  • August 27, 2014 at 1:23 pm

    #1741465

    ryetimothy (8/27/2014)

    Someone I work with created the wrong ODBC connection and entered data in the wrong database (old copy on an old server that's about to be decommissioned) and I need to move the data to the new location. Transferring most of the data will be a breeze but there's two tables that are causing me problems because one of the table is linked to the other using a foreign key. I'm concerned about maintaining the relational integrity of the tables and updating the foreign key in the child table based on the new primary key in the parent table. Could anyone give me some advice on how to proceed?

    Quick thought (if I got this right), copy both tables without the constraints to the correct server/database. Then run a query that returns any entries violating the foreign key constraints. If "three green" then reinstate the constraints.

    😎

  • August 28, 2014 at 10:00 am

    #1741686

    Pretty sure there won't be a problem with violations because the database in the new location hasn't been modified since it was migrated.

    Possibly dumb questions here: I import the parent table first and then the child table? Are the connections between the child table and the parent table recreated or is there something I have to do after the import to make sure to maintain referential integrity? What happens if there are key violations?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply