DROP COLUMN fails with constraint error 1

[SHelton]

I am trying to drop a column through TSQL, but get the following error:

Server: Msg 4922, Level 16, State 1, Line 9
ALTER TABLE DROP COLUMN D failed because DEFAULT CONSTRAINT DF__Estimate_Site__D__2C8964E2 accesses this column.

The code used to create the column is:

alter table Estimate_Site_AssetsAddList
add D decimal(9, 4) not null default 0 with values

I can remove the column through Enterprise Manager, but not through Query Analyser. Any ideas why?

 

[Kolt]

ALTER TABLE Estimate_Site_AssetsAddList
DROP constraint DF__Estimate_Site__D__2C8964E2
go
ALTER TABLE Estimate_Site_AssetsAddList
DROP column D
go

 

[SHelton]

Thanks for the quick response Kolt - this would work once, but if I recreate the column the constraint is added again with a different name (e.g. DEFAULT CONSTRAINT DF__Estimate_Site__D__39E36000).

I've worked out that the constraint is created for the NOT NULL I have specified. But how can I remove the constraint in order to allow me to drop the column at runtime, assuming that I would not know the name of the default constraint ?

I have tried altering the column to a nullable column first, but the drop still fails.

 

[JamesLean]

You can specify the name of the DEFAULT constraint like this:

CREATE TABLE t1 (
  col1 int NOT NULL CONSTRAINT default1 DEFAULT (0)
)

You can then reference the constraint by name when you need to drop it.

I always try and explicitly name all constraints (including PKs, FKs, DEFAULTS etc..) so they are more easily identifiable later on.

--James

 

[SHelton]

Many thanks James - not only works a treat but is good design advice too.