Drop Column 2

[ecobb]

I'm trying to drop some columns in a table, and for the most part, it's working. But I have a couple of them that give this error when I try to drop them.
Server: Msg 5074, Level 16, State 1, Line 2
The object 'DF_PurchaseOrders_Approved' is dependent on column 'Approved'.

I figured out that I'm getting this because I have a default value specified for these columns. Can someone please tell me how to specify in an ALTER statement how to "turn off" the default value?

Here's my DROP Statement:

ALTER TABLE Inventory DROP COLUMN Approved



Thanks

 

[JohnDTampaBay]

You will need to

ALTER TABLE Inventory DROP CONSTRAINT DF_PurchaseOrders_Approved

before you can

ALTER TABLE Inventory DROP COLUMN Approved

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[ecobb]

Thanks! That worked great on all of my columns except the last one. And it has me completely confused! When I run this code:

ALTER TABLE Inventory DROP CONSTRAINT DF_PurchaseOrders_CheckedOut
ALTER TABLE Inventory DROP COLUMN CheckedOut

I get this error:

Server: Msg 3728, Level 16, State 1, Line 1
'DF_PurchaseOrders_CheckedOut' is not a constraint.

So I take the DROP CONSTRAINT out, right? When I run this code:

ALTER TABLE Inventory DROP COLUMN CheckedOut

I get this error:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_InventoryOrders_CheckedOut' is dependent on column 'CheckedOut'.

What gives? Is there something completely obvious that I'm missing here?

Thanks

 

[JohnDTampaBay]

DF_InventoryOrders_CheckedOut is just another default constraint. It is possible to have several constraints on the same table, even on the same column. You will have to drop all constraints that reference a column before you can drop the column.

ALTER TABLE Inventory DROP CONSTRAINT DF_InventoryOrders_CheckedOut

ALTER TABLE Inventory DROP COLUMN CheckedOut

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sunila7]

Hi,
It looks as if u have a constraint DF_InventoryOrders_CheckedOut

Try this...
ALTER TABLE Inventory DROP CONSTRAINT DF_InventoryOrders_CheckedOut

ALTER TABLE Inventory DROP COLUMN CheckedOut

Does it work?
Sunil

 

[ecobb]

Thanks guys, that did it!
I didn't even realize (until you two pointed it out) that one constraint was "DF_InventoryOrders_" and the other was "DF_PurchaseOrders_".

Oops! Oh, well, Thanks again!

 

[Mack2]

I have a lot of columns to delete with contraints. Is there a quick way to do this. Or do I have to go and remove every constraint then delete the column?
Thanks