Ошибка alter table drop column

alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];
alter table CompanyTransactions drop column [Created];

4

ALTER TABLE MySchema.MyTable ADD 
  MyColumn int DEFAULT NULL;

ALTER TABLE MySchema.MyTable ADD 
  MyColumn int NULL,
  CONSTRAINT DF_MyTable_MyColumn DEFAULT NULL FOR MyColumn;

1

  alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];

1

1

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

Answers

• hi,

  before dropping the column, you have to drop the constraint based on that column.. in your case the default constraint.. you did not name it at creation time, so the engine will propose and use a system generated name for that constraint, in your case ‘DF__Employees__LastR__4BAC3F29′
  

  so drop it and you can proceed, as trivialy indicated in the following sample,

  SET NOCOUNT ON;
  USE tempdb;
  GO
  CREATE TABLE dbo.test (
   Id int NOT NULL
   );
  GO
  ALTER TABLE dbo.test
   ADD LastRaiseDate datetime NOT NULL DEFAULT ‘2005-01-01’;
  GO
  PRINT ‘this will throw an exception as a default constraint is set for that column’;
  ALTER TABLE dbo.test
   DROP COLUMN LastRaiseDate;
  GO
  PRINT ‘take the default name of the default constraint and drop it’;
  ALTER TABLE dbo.test
   DROP CONSTRAINT DF__test__LastRaiseD__023D5A04;

  PRINT ‘now you can drop the column’;
  ALTER TABLE dbo.test
   DROP COLUMN LastRaiseDate;

   
  GO
  DROP TABLE dbo.test;

  regards

  ---

  http://www.asql.biz — DbaMgr2k — DbaMgr and further SQL Tools http://www.hotelsole.com/ — http://www.hotelsolericcione.de

  • Proposed as answer by

    Monday, July 18, 2011 11:13 PM

  • Marked as answer by
    ryguy72
    Wednesday, July 20, 2011 1:22 PM

• >> wonder if there is a way for SQL Server to determine the Constraint automatically, or dynamically. 

  You can find out all constraint defined on a table using system stored procedure
  

  sp_helpconstraint
  

  in  your case EXEC sp_helpconstraint ‘Employees’;
  

  should list all constraint defined on that table.
  

  • Proposed as answer by
    Andrea Montanari
    Monday, July 18, 2011 11:22 PM
  • Marked as answer by
    ryguy72
    Wednesday, July 20, 2011 1:22 PM

Solution 4

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

You need to do a few things:

1. You first need to check if the constrain exits in the information schema
2. 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
3. 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

Comments

• 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
  

• It’s frustrating that Entity Framework doesn’t take care of this for us.

• @chakeda I agree

• To add to @malloc4k’s answer…you could expand the «Constraints» folder in SQL Server 2016 and it will show the «default» constraint names.

• @user2513019 don’t forget to upvote it it helped you

• I have similar situation. Problem is that my application is deployed to a lot of machines and all auto generated constraints have different names. How to deal with this sort of situation? Adding a long script that is generating sql from information_schema to the migration does not feel as a clean and good option.

• @MantasDaškevičius The best option there is to name the constraint instead of using auto generated names.

Recents