The object DF is dependent on column - Changing int to double

Question

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 lt Category gt  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

User · Accepted Answer

Try this   Remove the constraint DF Movies Rating  48CFD27E before changing your field type   The constraint is typically created automatically by the DBMS  SQL Server    To see the constraint associated with the table  expand the table attributes in Object explorer  followed by the category Constraints as shown below     You must remove the constraint before changing the field type

User · Answer

Solution   open database table - gt  expand table - gt  expand constraints and see this

User · Answer

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   gt  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   gt  c Int nullable  false

User · Answer

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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to entity-framework

Examples related to entity-framework-4

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