ALTER TABLE DROP COLUMN failed because one or more objects access this column
C#SqlSql ServerCode FirstC# Problem Overview
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
C# Solutions
Solution 1 - C#
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];
Solution 2 - C#
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.
Solution 3 - C#
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.
- Get Name of all Constraints using sp_helpconstraint which is a system stored procedure utility - execute following
exec sp_helpconstraint '<your table name>'
- 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) - 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
Solution 4 - C#
When you alter column datatype
you need to change constraint key
for every database
alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];
Solution 5 - C#
You need to do a few things:
-
You first need to check if the constrain exits in the information schema
-
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
-
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
Solution 6 - C#
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.
Solution 7 - C#
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
Solution 8 - C#
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);
}
Solution 9 - C#
Copy the default constraint name from the error message and type it in the same way as the column you want to delete.