Solved

Question on constrains

Posted on 2011-02-23
7
403 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 40

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 40

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 40

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
What are the recommended security measures to put in place? 19 91
T-SQL: need to reset a declared variable 4 31
SQL: Transformation or Pivot 3 34
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 shrink a transaction log file down to a reasonable size.

734 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