如何删除带有约束的列? [英] How to drop column with constraint?

问题描述

如何删除在 SQL Server 2008 中具有默认约束的列?

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

我的查询是

alter table tbloffers
drop column checkin

我收到以下错误

ALTER TABLE DROP COLUMN 签入失败，因为一个或多个对象访问此列.

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?

推荐答案

首先你应该删除有问题的DEFAULT约束，然后你可以删除列

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

alter table tbloffers drop constraint [ConstraintName]
go

alter table tbloffers drop column checkin

但该错误可能是由其他原因引起的 - 例如，为它们设置了 SCHEMABINDING 选项的用户定义函数或视图.

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

UPD:完全自动删除约束脚本:

UPD: Completely automated dropping of constraints script:

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'alter table tbloffers drop constraint ['+dc.NAME+N']'
    from sys.default_constraints dc
    JOIN sys.columns c
        ON c.default_object_id = dc.object_id
    WHERE 
        dc.parent_object_id = OBJECT_ID('tbloffers')
    AND c.name = N'checkin'
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

这篇关于如何删除带有约束的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！