[sql] The object 'DF__*' is dependent on column '*' - Changing int to double

Basically I got a table in my EF database with the following properties:

public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public string Image { get; set; }
public string WatchUrl { get; set; }
public int Year { get; set; }
public string Source { get; set; }
public int Duration { get; set; }
public int Rating { get; set; }
public virtual ICollection<Category> Categories { get; set; }

It works fine however when I change the int of Rating to be a double I get the following error when updating the database:

The object 'DF_Movies_Rating__48CFD27E' is dependent on column 'Rating'. ALTER TABLE ALTER COLUMN Rating failed because one or more objects access this column.

What's the issue?

This is the tsql way

 ALTER TABLE yourtable DROP CONSTRAINT constraint_name     -- DF_Movies_Rating__48CFD27E

For completeness, this just shows @Joe Taras's comment as an answer

I had this error trying to run a migration to work around it I renamed the column and re-generated the migration using

add-migration migrationname -force

in the package manager console. I was then able to run

update-database

successfully.

MS SQL Studio take care of when you delete the column but if you need to Delete Constraint Programmatically here is simple solution

Here’s a code snippet that’ll drop a column with a default constraint:

DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'__ColumnName__' AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)
IF EXISTS (SELECT * FROM syscolumns WHERE id=object_id('__TableName__') AND name='__ColumnName__')
EXEC('ALTER TABLE __TableName__ DROP COLUMN __ColumnName__')

Just replace TableName and ColumnName with the appropriate values. You can safely run this even if the column has already been dropped.

Bonus: Here’s the code to drop foreign keys and other types of constraints.

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__')
BEGIN
SELECT @ConstraintName = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__'
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)
END

Blog

When we try to drop a column which is depended upon then we see this kind of error:

The object 'DF__*' is dependent on column ''.

drop the constraint which is dependent on that column with:

ALTER TABLE TableName DROP CONSTRAINT dependent_constraint;

Example:

Msg 5074, Level 16, State 1, Line 1

The object 'DF__Employees__Colf__1273C1CD' is dependent on column 'Colf'.

Msg 4922, Level 16, State 9, Line 1

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

Drop Constraint(DF__Employees__Colf__1273C1CD):

ALTER TABLE Employees DROP CONSTRAINT DF__Employees__Colf__1273C1CD;

Then you can Drop Column:

Alter Table TableName Drop column ColumnName

While dropping the columns from multiple tables, I faced following default constraints error. Similar issue appears if you need to change the datatype of column.

The object 'DF_TableName_ColumnName' is dependent on column 'ColumnName'.

To resolve this, I have to drop all those constraints first, by using following query

DECLARE @sql NVARCHAR(max)=''     
SELECT @SQL += 'Alter table ' + Quotename(tbl.name) + '  DROP constraint ' + Quotename(cons.name) + ';'
 FROM SYS.DEFAULT_CONSTRAINTS cons 
 JOIN   SYS.COLUMNS col ON col.default_object_id = cons.object_id
 JOIN   SYS.TABLES tbl ON tbl.object_id = col.object_id
 WHERE  col.[name] IN ('Column1','Column2')

--PRINT @sql
EXEC Sp_executesql @sql 

After that, I dropped all those columns (my requirement, not mentioned in this question)

DECLARE @sql NVARCHAR(max)='' 
SELECT @SQL += 'Alter table ' + Quotename(table_catalog)+ '.' + Quotename(table_schema) + '.'+ Quotename(TABLE_NAME) 
               + '  DROP column ' + Quotename(column_name) + ';'
FROM   information_schema.columns where COLUMN_NAME IN ('Column1','Column2')  
--PRINT @sql
EXEC Sp_executesql @sql 

I posted here in case someone find the same issue.

Happy Coding!

I'm adding this as a response to explain where the constraint comes from. I tried to do it in the comments but it's hard to edit nicely there :-/

If you create (or alter) a table with a column that has default values it will create the constraint for you.

In your table for example it might be:

CREATE TABLE Movie (
    ...
    rating INT NOT NULL default 100
)

It will create the constraint for default 100.

If you instead create it like so

CREATE TABLE Movie (
  name VARCHAR(255) NOT NULL,
  rating INT NOT NULL CONSTRAINT rating_default DEFAULT 100
);

Then you get a nicely named constraint that's easier to reference when you are altering said table.

ALTER TABLE Movie DROP CONSTRAINT rating_default;
ALTER TABLE Movie ALTER COLUMN rating DECIMAL(2) NOT NULL;
-- sets up a new default constraint with easy to remember name
ALTER TABLE Movie ADD CONSTRAINT rating_default DEFAULT ((1.0)) FOR rating;

You can combine those last 2 statements so you alter the column and name the constraint in one line (you have to if it's an existing table anyways)

Solution :

open database table -> expand table -> expand constraints and see this

As constraint has unpredictable name, you can write special script(DropConstraint) to remove it without knowing it's name (was tested at EF 6.1.3):

public override void Up()
{    
    DropConstraint();
    AlterColumn("dbo.MyTable", "Rating", c => c.Double(nullable: false));
}

private void DropConstraint()
{
    Sql(@"DECLARE @var0 nvarchar(128)
          SELECT @var0 = name
          FROM sys.default_constraints
          WHERE parent_object_id = object_id(N'dbo.MyTable')
          AND col_name(parent_object_id, parent_column_id) = 'Rating';
          IF @var0 IS NOT NULL
              EXECUTE('ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [' + @var0 + ']')");
}

public override void Down()
{            
    AlterColumn("dbo.MyTable", "Rating", c => c.Int(nullable: false));    
}