31

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.

I have already tried to find the default constraints, as described here: SQL Server 2005 drop column with constraints

Unfortunately without any success :( The line returned is:

fkKeywordRolleKontakt 2 814625945 0 defEmptyString

And I cannot remove either of fkKeywordRolleKontakt and 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.


Edit 2: Solved

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.

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:

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 :)

Community
  • 1
  • 1
Simon A. Eugster
  • 4,114
  • 4
  • 36
  • 31

5 Answers5

41

Did you try first:

ALTER TABLE <tablename> DROP CONSTRAINT defEmptyString;

?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 3
    Yes, I did. I then got a message that defEmptyString isn't a constraint on this table. – Simon A. Eugster Feb 12 '10 at 18:48
  • 1
    So what happens with this query: SELECT OBJECT_NAME(parent_object_id) FROM sys.default_constraints WHERE name = 'defEmptyString'; -- ? Do you get a result, if so, is it this table? If you don't get a result, what about: SELECT type_desc FROM sys.objects WHERE name = 'defEmptyString'; --? – Aaron Bertrand Feb 12 '10 at 19:15
  • No result for the first one, and for the second one row: type_desc DEFAULT_CONSTRAINT – Simon A. Eugster Feb 15 '10 at 06:59
  • So sys.objects has a row, but sys.default_constraints does not? This is SQL Server 2008? Was it upgraded from 2000, and what is the compatibility level? Are you sure you are always using dbo. prefix when referencing the table that has this column? What user are you connecting as and what permissions does the user have? – Aaron Bertrand Feb 15 '10 at 07:08
  • 1
    Yes. sys.default_constraints has no row. It is SQL Server 2008, and the compatibility level is set to 2008. I'm quite sure the database itself has been run under 2000 because the code is from 2004. I'm connected as local user with (afaik) admin permissions. .dbo prefix: No, but I tested now, does not make any difference. – Simon A. Eugster Feb 15 '10 at 07:59
  • PS: Might the user defined constant (see edit in the question) cause a problem? – Simon A. Eugster Feb 15 '10 at 08:06
  • Okay, Problem solved. Sorry for the missing information, I didn't think it was important too (although I should have known better). Thank you for your answer and time! – Simon A. Eugster Feb 15 '10 at 09:47
  • The column is a user-defined type? Yes, that information certainly would have been relevant. – Aaron Bertrand Feb 15 '10 at 18:29
  • Please put the answer in as an answer, not an edit to the question. – Rob Grant Aug 12 '14 at 11:25
8

drop the constraint which 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.

Example: trying to drop some column 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.

Error clearly states that we need to delete DF__Employees__IsDel__15502E78 constraint

ALTER TABLE Employess 
DROP CONSTRAINT DF__Employees__IsDel__15502E78;

Drop Column: ALTER TABLE Employess DROP COLUMN IsDelted2

Majid Basirati
  • 2,665
  • 3
  • 24
  • 46
Jinna Balu
  • 6,747
  • 38
  • 47
5

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.

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:

ALTER TABLE dbo.tlkpRolleKontakt
    DROP CONSTRAINT FK_tlkpRolleKontakt_tlkpKeyword
EXECUTE sp_unbindefault N'dbo.tlkpRolleKontakt.fkKeywordRolleKontakt'
ALTER TABLE dbo.tlkpRolleKontakt
    DROP COLUMN fkKeywordRolleKontakt
Majid Basirati
  • 2,665
  • 3
  • 24
  • 46
Simon A. Eugster
  • 4,114
  • 4
  • 36
  • 31
2

use this script to cancel the checking of constraint :

ALTER TABLE  @tablename  NOCHECK CONSTRAINT  @constraintname 
masoud ramezani
  • 22,228
  • 29
  • 98
  • 151
  • There does not seem to be any constraint: Msg 11415, Level 16, State 1, Line 1 Object 'defEmptyString' cannot be disabled or enabled. This action applies only to foreign key and check constraints. Msg 4916, Level 16, State 0, Line 1 Could not enable or disable the constraint. See previous errors. – Simon A. Eugster Feb 15 '10 at 07:04
-1

I ran into a simpler solution.

  1. DELETE the data of that column.
  2. Once the column has no value inside it do -

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.

Sandman
  • 115
  • 3