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

Question

I am trying to do this:

ALTER TABLE CompanyTransactions DROP COLUMN Created

But I get this:

Msg 5074, Level 16, State 1, Line 2 The object 'DF__CompanyTr__Creat__0CDAE408' is dependent on column 'Created'. Msg 4922, Level 16, State 9, Line 2 ALTER TABLE DROP COLUMN Created failed because one or more objects access this column.

This is a code first table. Somehow the migrations have become all messed up and I am trying to manually roll back some changed.

I have no idea what this is:

DF__CompanyTr__Creat__0CDAE408

Marinemarinelli

Its not as straight forward as that. You won't be able to view the actual constraint name in higher environments. – Erigeron

Answer 1

You must remove the constraints from the column before removing the column. The name you are referencing is a default constraint.

e.g.

alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];
alter table CompanyTransactions drop column [Created];

Gothic

It's frustrating that Entity Framework doesn't take care of this for us. – Reply

@Reply I agree – Gothic

I have similar situation. Problem is that my application is deployed to a lot of machines and all auto generated constraints have different names. How to deal with this sort of situation? Adding a long script that is generating sql from information_schema to the migration does not feel as a clean and good option. – Hustler

@MantasDaškevičius The best option there is to name the constraint instead of using auto generated names. – Gothic

Answer 2

The @SqlZim's answer is correct but just to explain why this possibly have happened. I've had similar issue and this was caused by very innocent thing: adding default value to a column

ALTER TABLE MySchema.MyTable ADD 
  MyColumn int DEFAULT NULL;

But in the realm of MS SQL Server a default value on a colum is a CONSTRAINT. And like every constraint it has an identifier. And you cannot drop a column if it is used in a CONSTRAINT.

So what you can actually do avoid this kind of problems is always give your default constraints a explicit name, for example:

ALTER TABLE MySchema.MyTable ADD 
  MyColumn int NULL,
  CONSTRAINT DF_MyTable_MyColumn DEFAULT NULL FOR MyColumn;

You'll still have to drop the constraint before dropping the column, but you will at least know its name up front.

Crowning

To add to @malloc4k's answer...you could expand the "Constraints" folder in SQL Server 2016 and it will show the "default" constraint names. – Brockman

So in MySQL land, to alter a table and drop a column, I just, er, ALTER TABLE x DROP COLUMN y, and it just deals with any defaults on the column. But in SQL Server land, I have to have remembered to create the column with named "constraints" for any default values in the first place, then, when I want to drop the column, I have to drop the named constraint first before dropping the column... how typically Microsoft... why use one command when three will do? Is there no drop column cascade type option? – Percutaneous

I've just counted - I have 991 DEFAULT column values in my schema. That's a lot to have to add extra code for, just on the off-chance I might want to drop one of them in the future... – Percutaneous

Answer 3

As already written in answers you need to drop constraints (created automatically by sql) related to all columns that you are trying to delete.

Perform followings steps to do the needful.

1. Get Name of all Constraints using sp_helpconstraint which is a system stored procedure utility - execute following exec sp_helpconstraint '<your table name>'
2. Once you get the name of the constraint then copy that constraint name and execute next statement i.e alter table <your_table_name> drop constraint <constraint_name_that_you_copied_in_1> (It'll be something like this only or similar format)
3. Once you delete the constraint then you can delete 1 or more columns by using conventional method i.e Alter table <YourTableName> Drop column column1, column2 etc

Purifoy

Answer 4

When you alter column datatype you need to change constraint key for every database

  alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];

Propagandist

@user2513019 don't forget to upvote it it helped you :) – Porbeagle

Answer 5

You need to do a few things:

1. You first need to check if the constrain exits in the information schema
2. then you need to query by joining the sys.default_constraints and sys.columns if the columns and default_constraints have the same object ids
3. When you join in step 2, you would get the constraint name from default_constraints. You drop that constraint. Here is an example of one such drops I did.

-- 1. Remove constraint and drop column
IF EXISTS(SELECT *
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = N'TABLE_NAME'
            AND COLUMN_NAME = N'LOWER_LIMIT')
   BEGIN
    DECLARE @sql NVARCHAR(MAX)
    WHILE 1=1
        BEGIN
            SELECT TOP 1 @sql = N'alter table [TABLE_NAME] 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('[TABLE_NAME]') AND c.name = N'LOWER_LIMIT'
            IF @@ROWCOUNT = 0
                BEGIN
                    PRINT 'DELETED Constraint on column LOWER_LIMIT'
                    BREAK
                END
        EXEC (@sql)
    END;
    ALTER TABLE TABLE_NAME DROP COLUMN LOWER_LIMIT;
    PRINT 'DELETED column LOWER_LIMIT'
   END
ELSE
   PRINT 'Column LOWER_LIMIT does not exist'
GO

Erigeron

Thank you. With a variable for table/column names and it will be perfect ;) – Anomalous

Answer 6

In addition to accepted answer, if you're using Entity Migrations for updating database, you should add this line at the beggining of the Up() function in your migration file:

Sql("alter table dbo.CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];");

You can find the constraint name in the error at nuget packet manager console which starts with FK_dbo.

Glyco

Answer 7

I had the same problem and this was the script that worked for me with a table with a two part name separated by a period ".".

USE [DATABASENAME] GO ALTER TABLE [TableNamePart1].[TableNamePart2] DROP CONSTRAINT [DF__ TableNamePart1D__ColumnName__5AEE82B9] GO ALTER TABLE [TableNamePart1].[ TableNamePart1] DROP COLUMN [ColumnName] GO

Channel

Answer 8

I needed to replace an INT primary key with a Guid. After a few failed attempts, the EF code below worked for me. If you hyst set the defaultValue... you end up with a single Guid a the key for existing records.

protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropUniqueConstraint("PK_Payments", "Payments");

            migrationBuilder.DropColumn(
                name: "PaymentId",
                table: "Payments");

            migrationBuilder.AddColumn<Guid>(
                name: "PaymentId",
                table: "Payments",
                type: "uniqueidentifier",
                defaultValueSql: "NewId()",
                nullable: false);
}

Seka

Answer 9

I had the same problem, I could not remove migrations, it would show error that something is already applied, so i changed my DB name in appsettings, removed all migrations, and then added new migration and it worked. Dont understand issue completely, but it worked

Furlong

Answer 10

If you need to find the name of the constraint(s):

    SELECT d.name FROM sys.default_constraints d
    INNER JOIN sys.columns c
    ON d.parent_column_id = c.column_id
    WHERE d.parent_object_id = OBJECT_ID(N'HumanResources.Employee', N'U')
    AND c.name = 'VacationHours';

Do change the table name 'HumanResources.Employee' and the 'VacationHours'.

Modified from

Involutional

Answer 11

Copy the default constraint name from the error message and type it in the same way as the column you want to delete.

Drab

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Depopulate

Answer 12

I fixed by Adding Dropping constraint inside migration.

migrationBuilder.DropForeignKey(
         name: "FK_XX",
         table: "TableX").

and below recreates constraint.

migrationBuilder.AddForeignKey(
          name: "FK_XX",
          table: "TableX",
          column: "ColumnX",             
          onDelete: ReferentialAction.Restrict);

Zollverein