Solved

In which order I should drop the constraints and recreate them?

Posted on 2013-06-04
6
276 Views
Last Modified: 2013-07-07
I've a database in which I would like to do bulk schema changes. The DB does not have FK.  I would like to delete following constraints and recreate them later point. In which order I could drop the constraints and in which order I could recreate them?

PK, Default, Unique, NOT NULL, and CHECK, Clustured index, Non clustured index, Triggers

Please assist. Thanks
0
Comment
Question by:Easwaran Paramasivam
  • 3
6 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39219259
May I ask why you need to drop and recreate constraints?
Triggers are not really SQL constraints so you can drop them at any time or better DISABLE.
If you really need to drop them here's what I would do:

Save definition for ALL constriants you will drop then drop in order - constraints (null,check, unique, etc), index, clustered index, PKeys.

Add them back - Clustered index (take the longest), PK, Index, then all the rest of constraints.
IF and ONLY IF data was NOT added or you know 100000% the data integrity is OK you could add them WITH NOCHECK so it will not take too long to add them back in.
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39219455
Actually speaking I get constraints from one database and run in another database (after dropping all constraints) in order to sync the two database in all respect. Please provide solution.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39224563
Hi,

If you need to sync the db schema of two dbs you may check on some of third party tools available on the web. I have used DBGhost in the past and found it very helpful. You may define a source and a target db, and it will check for any schema differences. Then it will prompt for auto creating scripts to sync the target db.

Giannis
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39253692
@lcohan: Does below order correct?

Dropping order is null, check, unique, default, non clustered index, clustered index, PKeys.

Add them back - Clustered index, PK,  Non clustered Index, default, unique, check, Null
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39306278
Thanks
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now