Reputation: 13965
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
Upvotes: 124
Views: 250361
Reputation: 21
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'.
Upvotes: 1
Reputation: 472
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);
Upvotes: 0
Reputation: 55
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
Upvotes: 1
Reputation: 1
Copy the default constraint name from the error message and type it in the same way as the column you want to delete.
Upvotes: 0
Reputation: 429
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);
}
Upvotes: 2
Reputation: 31
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
Upvotes: 3
Reputation: 529
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.
Upvotes: 4
Reputation: 2324
You need to do a few things:
-- 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
Upvotes: 7
Reputation: 71
When you alter column datatype
you need to change constraint key
for every database
alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];
Upvotes: 7
Reputation: 6548
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.
exec sp_helpconstraint '<your table name>'
alter table <your_table_name>
drop constraint <constraint_name_that_you_copied_in_1>
(It'll be something like this only or similar format)Alter table <YourTableName> Drop column column1, column2
etcUpvotes: 13
Reputation: 1937
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.
Upvotes: 44
Reputation: 38063
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];
Upvotes: 216