0

I've created a column called "Identified" and I've assigned a default value of "1". Now I realized that I would like to delete that column. But when I try I get the following error:

Msg 5074, Level 16, State 1, Line 5
The object 'DF__orders__Identifi__403A8C7D' is dependent on column 'Identified'.

Msg 4922, Level 16, State 9, Line 5
ALTER TABLE DROP COLUMN Identified failed because one or more objects access this column.

Here is the code used:

ALTER TABLE BikeStores.sales.orders 
    DROP COLUMN Identified;

I've also tried:

ALTER TABLE BikeStores.sales.orders 
    DROP Identified;

But in this case the error get is:

Msg 3728, Level 16, State 1, Line 5
'Identified' is not a constraint.

Msg 3727, Level 16, State 0, Line 5
Could not drop constraint. See previous errors.

Any tip on how to fix this error? Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
happy_hippo
  • 33
  • 4

1 Answers1

3

The column cannot be dropped because the default constraint is dependant on the column existing. Drop the default constraint first.

If the version of SQL server you are working with is 2016 or before you can use the simple query:

ALTER TABLE BikeStores.sales.orders ALTER COLUMN Identified DROP DEFAULT;

From 2017 onwards you will need to explicitly use the default constraints name

ALTER TABLE BikeStores.sales.orders DROP CONSTRAINT DF__orders__Identifi__403A8C7D;

If you do not know the name of the default (due to it being auto named on creation) the following script will identify and remove the default.

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @schema_name = N'sales'
set @table_name = N'orders'
set @col_name = N'Identitified'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name
 from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
 where t.name = @table_name
  and t.schema_id = schema_id(@schema_name)
  and c.name = @col_name

EXEC sp_executesql @Command

(credit to this question for the last part ) How to drop SQL default constraint without knowing its name?

You will then be able to drop your column

MarkD
  • 1,043
  • 12
  • 18
  • 2
    This command does not work. At least not in my SQL Server 2019 CU9 instance. I have to explicitly drop the constraint like this: `ALTER TABLE BikeStores.sales.orders DROP CONSTRAINT DF__orders__Identifi__403A8C7D;` – Bart Hofland Mar 18 '21 at 11:38
  • @BartHofland ah thanks, looks like that functionality was removed after 2016. will update my answer – MarkD Mar 18 '21 at 12:38
  • You're welcome. :) I have been using SQL Server since version 2000, but I was not aware that that ALTER TABLE ALTER COLUMN DROP DEFAULT statement was available in pre-2017 versions. Nice to know anyway. :) I have always used the ALTER TABLE DROP CONSTRAINT statement and I have always insisted on creating explicitly named default constraints just for the case when you need to drop them at any future time. – Bart Hofland Mar 18 '21 at 12:45
  • 1
    yeah, like you i tend to drop by name (as naming all of your constraints is the way to go :P) however as the question had an auto generated name I just added in the "easy syntax". We moved over to db projects/deployments before we moved away from 2016 so I hadn't realised the functionality had changed since we started using dacs to make structural changes – MarkD Mar 18 '21 at 12:49
  • Hello @MarkD, I've tried your solution (`ALTER TABLE BikeStores.sales.orders ALTER COLUMN Identified DROP DEFAULT;`) and it's not working. Error message: "Incorrect syntax near the keyword 'DEFAULT'." Any tips? – happy_hippo Mar 18 '21 at 19:45
  • @happy_hippo which version of sql server is installed? – MarkD Mar 22 '21 at 04:20