Solved

Question on constrains

Posted on 2011-02-23
7
404 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

688 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