Disabling constraint check

Posted on 2004-11-05
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,
Question by:ghassan99
    LVL 8

    Accepted Solution

    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
    LVL 18

    Expert Comment

    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
    LVL 6

    Author Comment

    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:


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


    LVL 8

    Expert Comment

    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.

    LVL 18

    Expert Comment

    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.
    LVL 18

    Expert Comment

    Do you need any more assistance with this question ?

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    754 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

    17 Experts available now in Live!

    Get 1:1 Help Now