?
Solved

Disabling constraint check

Posted on 2004-11-05
9
Medium Priority
?
1,358 Views
Last Modified: 2008-01-09
Hi Experts,
I'm trying to change the collation of many fields in the database.  I'm using Alter table...collate
I have generated a long script to change all the fields of type char and varchar in all the tables, there are thousands of fields.
When I start the script I get this error

The index abc is dependent on column xyz
failed because one or more objects access this column

When I change the collation via Enterprise manager on the same field or column I dont get an error message and the change is successful.
I cannot drop the constraint on each column via script, coz as I said earlier I have thousands of fields.
Is there a way to stop SQL server constraint check

Thanks for any feedback,
-Gus
0
Comment
Question by:ghassan99
  • 3
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
SashP earned 1000 total points
ID: 12509223
There is no way to disable the constraint.  You need to drop it then re-create it.

If you want the code to drop and recreate all your scripts.

You do not need to code it as enterprise manager does it for you.

Right click on the database and select Generate SQL Script.  Select all tables and the options to generate drop and generate constraints.

It will generate all the script to drop all the constrints tables and recreate them.  Trim out all the table drop and create code and insert you alter table script.

Cheers Sash
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12510075
sash is correct.     SQL Enterprise mrg basically scripts up the constraints (on the quiet) and drops them, applies the change and recreates the constraints.

If you wanted to simulate this behaviour you would need to refernce SQL DMO (this is what enterprise manager does)  or write lots and lots and lots of tsql
0
 
LVL 6

Author Comment

by:ghassan99
ID: 12511767
One reason I wont go this route is becauase I have huge data in the database.  I cant drop the tables.
I know there is one command to stop/disable SQL checking on the constraints:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

My problem really lies with the indexes, I need to drop the indexes and re-create them after the alter.

-Gus




0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:SashP
ID: 12511813
As far as I am aware

You can not change the collation of a column that has a check constraint regardless of whether the check constraint has been disabled or not.

By disabling the check constraint you prevent the check constraint from checking data inserts updates etc. but you will still encounter the  errors

Server: Msg 5074, Level 16, State 6, Line x
The object '[Constraint Name]' is dependent on column '[Column Name].
Server: Msg 4922, Level 16, State 1, Line x
ALTER TABLE ALTER COLUMN [Column Name] failed because one or more objects access this column.

I am not aware of a method that will allow you to change the collation on the live database without dropping the objects that reference the column, including your constraints and indexes.

Sash
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12523598
Depending on the To & From collation it is possible to simply modify the system tables directly, I know of a number of peaople that have done this.   However I do not ensorse this approach as it is extremely dangerous.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12655134
Do you need any more assistance with this question ?
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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

807 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