Trying to delete a Default, but getting a message that it doesn't exist on the server

Question

I need to alter a data type of a column from smallint to bigint (poor design consideration, don't ask). I run the TSQL

alter table dbo.Test alter column ProductionModeID bigint NULL

But get the message:

Msg 5074, Level 16, State 1, Line 1 The object 'DF_Test_ProductionModeID' is dependent on column 'ProductionModeID'.

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

I have found DF_Test_ProductionModeID in dbo.Test >> Constraints. (I don't know how DF_Test_ProductionModeID was created, nor do I believe I need it to exist). I press Delete, choose OK button, and get the following message:

===================================

The Default 'DF_Test_ProductionModeID' does not exist on the server. (SqlManagerUI)

---

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476

---

Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DoDropObject(Int32 objectRowIndex) at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DropAllObjects(Boolean stopOnError)

So I'm now confused, because if it doesn't exist, then how can it depend on a column?

Answer 1

What happens when you run the following?

ALTER table Test drop DF_Test_ProductionModeID

See also How To Name Default Constraints And How To Drop Default Constraint Without A Name In SQL Server for some more info