drop constraint ms sql

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Удалить проверочное ограничение в SQL Server SQL Server можно при помощи SQL Server Management Studio SQL Server Management Studio или Transact-SQL Transact-SQL . You can delete a check constraint in SQL Server SQL Server by using SQL Server Management Studio SQL Server Management Studio or Transact-SQL Transact-SQL . Удаление проверочного ограничения снимает ограничения на значения данных, допустимые для столбца или столбцов, включенных в выражение ограничения. Deleting check constraints removes the limitations on data values that are accepted in the column or columns included in the constraint expression.

В этом разделе In This Topic

Перед началом работы Before you begin:

Удаление проверочного ограничения с использованием: To delete a check constraint, using:

Перед началом Before You Begin

безопасность Security

Permissions Permissions

Требуется разрешение ALTER на таблицу. Requires ALTER permission on the table.

Использование среды SQL Server Management Studio Using SQL Server Management Studio

Удаление проверочного ограничения To delete a check constraint

В Обозревателе объектовразверните таблицу с проверочным ограничением. In Object Explorer, expand the table with the check constraint.

Разверните Ограничения. Expand Constraints.

Щелкните ограничение правой кнопкой мыши и выберите Удалить. Right-click the constraint and click Delete.

В диалоговом окне Удаление объекта нажмите кнопку ОК. In the Delete Object dialog box, click OK.

Использование Transact-SQL Using Transact-SQL

Удаление проверочного ограничения To delete a check constraint

В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine Database Engine . In Object Explorer, connect to an instance of Компонент Database Engine Database Engine .

На стандартной панели выберите пункт Создать запрос. On the Standard bar, click New Query.

Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute.

Дополнительные сведения см. в разделе ALTER TABLE (Transact-SQL). For more information, see ALTER TABLE (Transact-SQL).

DROP CONSTRAINT

The DROP CONSTRAINT command is used to delete a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint.

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

SQL Server / Oracle / MS Access:

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

SQL Server / Oracle / MS Access:

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

SQL Server / Oracle / MS Access:

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

COLOR PICKER

HOW TO

SHARE

CERTIFICATES

Your Suggestion:

Thank You For Helping Us!

Your message has been sent to W3Schools.

Top Tutorials

Top References

Top Examples

Web Certificates

W3Schools is optimized for learning, testing, and training. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using this site, you agree to have read and accepted our terms of use, cookie and privacy policy. Copyright 1999-2019 by Refsnes Data. All Rights Reserved.
Powered by W3.CSS.

How to drop a column which is having Default constraint in SQL Server 2008?

I am getting below error

ALTER TABLE DROP COLUMN checkin failed because one or more objects access this column.

Can anyone correct my query to drop a column with constraint?

8 Answers 8

First you should drop the problematic DEFAULT constraint , after that you can drop the column

But the error may appear from other reasons — for example the user defined function or view with SCHEMABINDING option set for them.

UPD: Completely automated dropping of constraints script:

Here’s another way to drop a default constraint with an unknown name without having to first run a separate query to get the constraint name:

Find the default constraint with this query here:

This gives you the name of the default constraint, as well as the table and column name.

When you have that information you need to first drop the default constraint:

and then you can drop the column

You can also drop the column and its constraint(s) in a single statement rather than individually.

Some dynamic SQL that will look up the names of dependent check constraints and default constraints and drop them along with the column is below

(but not other possible column dependencies such as foreign keys, unique and primary key constraints, computed columns, indexes)

The following worked for me against a SQL Azure backend (using SQL Server Management Studio), so YMMV, but, if it works for you, it’s waaaaay simpler than the other solutions.

ALTER TABLE DROP COLUMN failed because one or more objects access this column message.

My column had an index which needed to be deleted first. Using sys.indexes did the trick:

I have updated script a little bit to my SQL server version

It’s not always just a default constraint that prevents from droping a column and sometimes indexes can also block you from droping the constraint. So I wrote a procedure that drops any index or constraint on a column and the column it self at the end.