Foreign Keys while importing a dump
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Foreign Keys while importing a dump

  1. #1
    Join Date
    Sep 2005
    Posts
    46

    Foreign Keys while importing a dump

    Dear All,

    While importing dump into a schema which is already having the tables, how to deal with the foreign key constraints. Its possible to disable all the constraints and then enable it after the import.

    Apart from that, is there any other ways to handle it in the import options? I'm using normal imp.exe

    Is it possible to do it using data pump?


    regards
    issac

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You can either disable all FKs and then enable them back OR -this is what I would do, drop all FKs, import from a dump file created with CONSTRAINTS=N option and then create all FKs with NOVALIDATE option.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    you are not specified what the error got at the time on import...

    try this....

    You will have to run the structural export with CONSTRAINTS=Y and the data export with CONSTRAINTS=N. Follow these steps:

    Run the first export with parameter ROWS=N and parameter CONSTRAINTS=Y.
    Run the second export with ROWS=Y and CONSTRAINTS=N.
    Run the first import to build the tables with constraints.
    Disable all foreign key constraints using ALTER TABLE DISABLE CONSTRAINT
    .

    5. Run the second import that imports data without building constraints.

    6. Enable all foreign key constraints using ALTER TABLE ENABLE CONSTRAINT .

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gopu_g
    you are not specified what the error got at the time on import...
    Poster did not get any error, that's why
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width