SQL Constraints?

[dhoward007]

Has anyone ever had to drop constraints on one schema, truncate data and then move data from an identicle schema and then recreated the constraints..


My question is, is there an easy way to do something like this?? I have tried it a few times and for some reason I get some primary key constraint violation when I try this.. I tried to first drop all constraints, then truncate all of the tables on the destination database, then using DTS, I used the transfer objects and choose copy data only. I get the primary key constrain violations...

The database are 2/3 normal form and use constraints heavily.. I need to find a quick and dirty way to move data only from one database to another.. One server is Production and the other is QA..

any suggestions?? I find it hard to do this in SQL..I used to work with oracle and it seems a little easier to work with the data dictionary..

 

[fluteplr]

If they are really identical, why not just do a backup on one and restore it to the other?

 

[ClayG]

If you were dropping all constraints, that would include the primary key constraints and you couldnt get a primary key violation when loading data, only when you attempt to recreate the primary key contraint. Or maybe you only dropped foreign key contraints ? Also, if the data fails some constraints then there were not identical constraints in the source database (unless NOCHECK was used on the source database).

Anyway, I would say it only makes sense to drop foreign keys constraints. No point dropping primary keys, to create them later. Check constraints are arguable.

If your data fails primary key constraints then you must have different primary key (or none) on the source database. Fix source data, or Select data with a Group By on the primary key columns.

Another potential trap is you not only need to drop foreign keys ON the target table, but any foreign keys that REFERENCE the target table. E.G. before truncating Customers, you must drop foreign key on Transactions that references Customers.