Trying to use table memory optimization advisor on SQL Server 2016, seems I need to drop and re-create the foreign keys of some disk based tables, the size of the table is 300 GB and I'm wondering if there is any risk to drop and re-create the foreign keys before table memory migration?

enter image description here

  • 1
    FYI-Foreign Key relationship between the memory optimized and non-memory optimized table is not supported in SQL Server 2016. Aug 15, 2017 at 18:57

1 Answer 1


Risk is in between you drop and recreate the Foreign key constraint any process (application, user etc etc) will be allowed to insert data in any one of these tables which can break the referential integrity. If that happens you will not be able to recreate the Foreign key constraint (with checking integrity on existing data, which is a good thing) and you will have to clean up the data.

There are ways to tell SQL Server to ignore existing data and enforce the constraint only on new data. Which is BAD or GOOD depends on your business need.

Details here:

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.