Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Import from external database, but switch off foreign key constraints.

Hi all,

I've upgraded a local database, and need to import data from the live. But, I can't seem to do this as it won't let me TRUNCATE the tables (through the import process) as they're used in FK constraints, and I know the it won't let me import out of sequence due to FK constraints.

Is there any way to disable the constraints for me to import??????
0
AlfaNoMore
Asked:
AlfaNoMore
  • 3
  • 2
1 Solution
 
ispalenyCommented:
0
 
JulianvaCommented:
Existing FOREIGN KEY constraints can be disabled for:

INSERT and UPDATE statements, thereby allowing data in the table to be modified without being validated by the constraints. Disable a FOREIGN KEY constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.

The column has a value that violates the constraint; therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow the constraint to be added.

run this query against the table

ALTER TABLE your table WITH NOCHECK




0
 
AlfaNoMoreAuthor Commented:
Does the ALTER TABLE your table WITH NOCHECK work on a transcation basis, or does it set it globally? The reaon I'm asking is that I'm using the DTS data import wizard from SQL, so won't be scripting anything manually.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ispalenyCommented:
1. Save DTS package in DTS data import wizard
2. Modify DTS package in EM. In Transform Data Task,Options, uncheck "Check constraints" box.
3. Run DTS package

But I am not sure, what will be the result. Fully functional constraints, or virtual constraints working for new data only and not for a Query Optimiser engine.
0
 
JulianvaCommented:
It will alter that table only

use query analyser to run the alter table with no check first


check books on line for more info

0
 
ispalenyCommented:
Use command to switch off FKs
ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL

Use command to switch on FKs
ALTER TABLE YourTableName CHECK CONSTRAINT ALL

I tried it. It is secure for Query Optimiser, but it checks referential integrity of new data only.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now