Improve company productivity with a Business Account.Sign Up

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

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

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
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
  • 3
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
Easwaran ParamasivamAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
Easwaran ParamasivamAuthor Commented:
@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
 
Easwaran ParamasivamAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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