Ошибка alter table alter column так как один или несколько объектов обращаются к данному столбец

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

asked Apr 21, 2017 at 18:11

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

answered Dec 27, 2018 at 10:34

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

answered Aug 7, 2019 at 20:16

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.

answered Oct 17, 2020 at 15:10

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

answered Nov 8, 2020 at 14:01

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);
}

answered Apr 24, 2021 at 6:56

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

answered Jan 17 at 14:42

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

answered Feb 7 at 9:11

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

asked Apr 21, 2017 at 18:11

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

answered Dec 27, 2018 at 10:34

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

answered Aug 7, 2019 at 20:16

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.

answered Oct 17, 2020 at 15:10

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

answered Nov 8, 2020 at 14:01

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);
}

answered Apr 24, 2021 at 6:56

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

answered Jan 17 at 14:42

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

answered Feb 7 at 9:11