Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Question on constrains

Posted on 2011-02-23
7
Medium Priority
?
406 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 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.
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 15

Assisted Solution

by:derekkromm
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)
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 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.
0
 
LVL 40

Assisted Solution

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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.

604 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