如何在 SQL Server 2008 中删除具有对象依赖关系的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2253185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I drop a column with object dependencies in SQL Server 2008?
提问by Simon A. Eugster
The error message I'm obtaining when trying to drop a column:
我在尝试删除列时收到的错误消息：
The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'.
Msg 5074, Level 16, State 1, Line 43
ALTER TABLE DROP COLUMN fkKeywordRolleKontakt failed because one or more objects access this column.
对象“defEmptyString”依赖于“fkKeywordRolleKontakt”列。
消息 5074，级别 16，状态 1，第 43 行
ALTER TABLE DROP COLUMN fkKeywordRolleKontakt 失败，因为一个或多个对象访问此列。
I have already tried to find the default constraints, as described here: SQL Server 2005 drop column with constraints
我已经尝试找到默认约束，如下所述： SQL Server 2005 drop column with constraint
Unfortunately without any success :( The line returned is:
不幸的是没有任何成功:(返回的行是：
fkKeywordRolleKontakt 2 814625945 0 defEmptyString
And I cannot remove either of fkKeywordRolleKontaktand defEmptyString.
而且我无法删除fkKeywordRolleKontakt和中的任何一个defEmptyString。
What is the correct way to get rid of this dependency?
摆脱这种依赖的正确方法是什么？
EDIT: Perhaps this is of importance too. The column fkKeywordRolleKontakt is of type udKeyword (nvarchar(50)) with default dbo.defEmptyString.
编辑：也许这也很重要。fkKeywordRolleKontakt 列的类型为 udKeyword (nvarchar(50))，默认为dbo.defEmptyString。
Edit 2: Solved
编辑 2：已解决
I could solve the problem now. I'm sorry, I did not copy the full error message, which was:
我现在可以解决问题了。抱歉，我没有复制完整的错误消息，即：
Msg 5074, Level 16, State 1, Line 1
The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK_tlkpRolleKontakt_tlkpKeyword' is dependent on column 'fkKeywordRolleKontakt'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN fkKeywordRolleKontakt failed because one or more objects access this column.
Msg 5074, Level 16, State 1, Line 1
The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK_tlkpRolleKontakt_tlkpKeyword' is dependent on column 'fkKeywordRolleKontakt'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN fkKeywordRolleKontakt failed because one or more objects access this column.
I could generate a script to drop the column by right-clicking on the column entry (dbo.tlkpRolleKontakt > Columns > fkKeywordRolleKontakt) (in MSSQL Server Manager), selecting Modify and deleting the column. Then Table Designer > Generate Change Script generated the necessary commands:
我可以通过右键单击列条目 (dbo.tlkpRolleKontakt > Columns > fkKeywordRolleKontakt)（在 MSSQL Server 管理器中）生成一个脚本来删除该列，选择修改并删除该列。然后 Table Designer > Generate Change Script 生成了必要的命令：
ALTER TABLE dbo.tlkpRolleKontakt
DROP CONSTRAINT FK_tlkpRolleKontakt_tlkpKeyword
EXECUTE sp_unbindefault N'dbo.tlkpRolleKontakt.fkKeywordRolleKontakt'
ALTER TABLE dbo.tlkpRolleKontakt
DROP COLUMN fkKeywordRolleKontakt
That's it :)
就是这样 ：）
采纳答案by Simon A. Eugster
I could solve the problem now. I'm sorry, I did not copy the full error message, which was:
我现在可以解决问题了。抱歉，我没有复制完整的错误消息，即：
Msg 5074, Level 16, State 1, Line 1
The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'.
消息 5074，级别 16，状态 1，第 1 行
对象“defEmptyString”依赖于列“fkKeywordRolleKontakt”。
Msg 5074, Level 16, State 1, Line 1
The object 'FK_tlkpRolleKontakt_tlkpKeyword' is dependent on column 'fkKeywordRolleKontakt'.
Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN fkKeywordRolleKontakt failed because one or more objects access this column.
消息 5074，级别 16，状态 1，第 1 行
对象“FK_tlkpRolleKontakt_tlkpKeyword”依赖于列“fkKeywordRolleKontakt”。
消息 4922，级别 16，状态 9，第 1 行 ALTER TABLE DROP COLUMN fkKeywordRolleKontakt 失败，因为一个或多个对象访问此列。
I could generate a script to drop the column by right-clicking on the column entry (dbo.tlkpRolleKontakt > Columns > fkKeywordRolleKontakt) (in MSSQL Server Manager), selecting Modify and deleting the column. Then Table Designer > Generate Change Script generated the necessary commands:
我可以通过右键单击列条目 (dbo.tlkpRolleKontakt > Columns > fkKeywordRolleKontakt)（在 MSSQL Server 管理器中）生成一个脚本来删除该列，选择修改并删除该列。然后 Table Designer > Generate Change Script 生成了必要的命令：
ALTER TABLE dbo.tlkpRolleKontakt
DROP CONSTRAINT FK_tlkpRolleKontakt_tlkpKeyword
EXECUTE sp_unbindefault N'dbo.tlkpRolleKontakt.fkKeywordRolleKontakt'
ALTER TABLE dbo.tlkpRolleKontakt
DROP COLUMN fkKeywordRolleKontakt
回答by Aaron Bertrand
Did you try first:
你有没有先尝试：
ALTER TABLE <tablename> DROP CONSTRAINT defEmptyString;
?
?
回答by Jinna Balu
drop the constraintwhich is dependent on that column with
删除依赖于该列的约束
ALTER TABLE TableName DROP CONSTRAINT dependent_constraint
Then Drop Column:
然后删除列：
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME
dependent_constraint: this constraint is shown in the error when we try to delete dependent column.
dependent_constraint：当我们尝试删除依赖列时，此约束显示在错误中。
Example: trying to drop some column IsDeleted2
示例：尝试删除某些列 IsDeleted2
Error
错误
The object 'DF__Employees__IsDel__15502E78' is dependent on column 'IsDeleted2'.
ALTER TABLE DROP COLUMN IsDeleted2 failed because one or more objects access this column.
对象“DF__Employees__IsDel__15502E78”依赖于“IsDeleted2”列。
ALTER TABLE DROP COLUMN IsDeleted2 失败，因为一个或多个对象访问此列。
Error clearly states that we need to delete DF__Employees__IsDel__15502E78constraint
错误明确指出我们需要删除DF__Employees__IsDel__15502E78约束
ALTER TABLE Employess
DROP CONSTRAINT DF__Employees__IsDel__15502E78;
Drop Column:ALTER TABLE Employess DROP COLUMN IsDelted2
删除列：ALTER TABLE Employess DROP COLUMN IsDelted2
回答by masoud ramezani
use this script to cancel the checking of constraint :
使用此脚本取消约束检查：
ALTER TABLE @tablename NOCHECK CONSTRAINT @constraintname
回答by Sandman
I ran into a simpler solution.
我遇到了一个更简单的解决方案。
- DELETE the data of that column.
- Once the column has no value inside it do -
- 删除该列的数据。
- 一旦该列内部没有任何值，请执行以下操作 -
ALTER TABLE <table_name> DROP COLUMN <column_name>
ALTER TABLE <table_name> DROP COLUMN <column_name>
This way the column is easily dropped.
通过这种方式，该列很容易掉落。
P.S - This is a headache if you have like extreme amounts of data in the column.
PS - 如果您喜欢列中的大量数据，这会令人头疼。
