Solved

Question on constrains

Posted on 2011-02-23
7
398 Views
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:


ALTER TABLE [dbo].[Cases] ADD PRIMARY KEY NONCLUSTERED  ([Id])
GO

or

ALTER TABLE [dbo].[LawFirm] ADD PRIMARY KEY CLUSTERED  ([FirmId])
GO

I am using MS SQL 2008 Server
0
Comment
Question by:amucinobluedot
7 Comments
 
LVL 39

Expert Comment

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

Accepted Solution

by:
lcohan earned 250 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.
0
 

Author Comment

by:amucinobluedot
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
GO

Also,

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

ADD PRIMARY KEY CLUSTERED
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 125 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)
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34965816
to drop constraints, you simply do

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

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 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.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

12 Experts available now in Live!

Get 1:1 Help Now