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:
This question is tagged with
~ Asked on 2017-04-21 18:11:28
You must remove the
constraints from the column before removing the column. The name you are referencing is a
alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408]; alter table CompanyTransactions drop column [Created];
~ Answered on 2017-04-21 18:14:26
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.
~ Answered on 2018-01-30 19:13:59