Question on constrains

Posted on 2011-02-23
Medium Priority
Last Modified: 2012-06-21
I have two databases I am trying to sync with RedGate's  SQL Compare.
Objects such as tables have slight differences and thats OK, but one database has one constrain in that object and another has a different constrain in the same object. Don't ask me why.

Question:  What happens if I drop the constrains on both databases so I can run my sync ?
Question: Is there a way to drop ALL constrains in all tables of a database, if so .. how ?  if not, how do I drop constrains from one table ?
Question: What could happen if I drop constrins from one table

My constrains look something similar to:




I am using MS SQL 2008 Server
Question by:Aleks
LVL 40

Expert Comment

ID: 34964598
You could get duplicate ID's and that's not good from data integrity point of view.
LVL 40

Accepted Solution

lcohan earned 1000 total points
ID: 34964624
You could generate/run some scripts to save all constraints definition, drop them, do the work you want and:

a. add them back WITH CHECK to revalidate data integrity
b. if you don't care about data integrity you could add them WITH NOCHECK leaving orphans and/or duplicates in tables.

Of course all these are NOT to be done on live production databases.

Author Comment

ID: 34964660
What would i need to do to drop one specific constrain ?
Not a key, but another constrain, such as:

-- Foreign Keys

ALTER TABLE [dbo].[ActivityParties] WITH NOCHECK ADD FOREIGN KEY ([activity_id]) REFERENCES [dbo].[Activities] ([ActivityId]) ON DELETE CASCADE


What is the difference between a 'clustered' and a nonclustered key ?

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

LVL 15

Assisted Solution

derekkromm earned 500 total points
ID: 34965812
a clustered index indicates the order in which the table physically stores data

a nonclustered index indicates an ordering cache, per se

so if you have a table with columns A, B, and C, and its clustered on A, it is physically storing the data ordered by A. however, you can also have a nonclustered index on C so that you can easily seek the data on C (for example, if C has dates and you frequently select data from the table based on a range of dates)
LVL 15

Expert Comment

ID: 34965816
to drop constraints, you simply do

alter table <table> drop constraint <constraint name>
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 34967091
Perhaps I am stating the obvious, but you do realize that SQL Compare will generate all the DROP/CREATE statements necessary in order to sync the tables.  There is no need for you to do that.  Just decide how you want them and then go for it.

Incidentally, not sure if that was a typo, but those two constraints appear to be on different tables.
LVL 40

Assisted Solution

lcohan earned 1000 total points
ID: 34970147
And from performance point of view - dropping a CLUSTERED "constraint" will be quick especially if db is not live under load however to add it back on large tables.....different story. Good luck.

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

597 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