DMS Requirement to Disable Foreign Keys

0

A number of DMS documents seem to indicate that it is a requirement to disable foreign key constraints when performing a full load \[1,2,3,4]. However, in my initial tests, I've found that this doesn't seem to be necessary, despite the fact that I am migrating data with foreign key relationships.

Specifically, I'm migrating an on-premise Oracle database to an RDS Oracle database, using the full-load-and-cdc option. My complete schema has already been created on the target as a prerequisite to the migration (therefore, I'm using the DO_NOTHING table preparation mode), including all foreign key constraints. I have successfully migrated the data from all 16 tables, which have related data constrained by foreign keys, without having to disable the foreign key constraints (nor make any other changes to my target schema).

Can someone elaborate on the conditions in which foreign keys must/should be disabled for migration?

[1] https://d0.awsstatic.com/whitepapers/RDS/AWS_Database_Migration_Service_Best_Practices.pdf
[2] http://docs.aws.amazon.com/dms/latest/sbs/CHAP_On-PremOracle2Aurora.Steps.CreateTask.html
[3] http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.html
[4] https://d0.awsstatic.com/whitepapers/strategies-for-migrating-oracle-database-to-aws.pdf

asked 7 years ago3372 views
1 Answer
0

I've confirmed that foreign keys are not always required to be disabled; they only need to be disabled under certain conditions. For example, per the above when using the direct path load [1] along with the "do nothing" table preparation mode [2], foreign keys need not be disabled. However, when using the conventional load or other table preparation modes, they must be disabled. There may be other conditions to consider as well.

[1] http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.ConnectionAttributes.html#CHAP_Introduction.ConnectionAttributes.Oracle

[2] http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.html

answered 7 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions