SQL Query Problem

[nightshift]

Using Query Analyzer, I am getting some results I have not seen before.

Any help that you can offer would be greatly appreciated.

Adding the Column works fine:

ALTER TABLE [dbo].[tblSaleItem] ADD [Temp_Qty] [float] DEFAULT 0 NOT NULL

Attempting to drop a column in a valid table using;

ALTER TABLE [dbo].[tblSaleItem] DROP COLUMN [Temp_Qty]

Gets the following error:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tblSaleIt__Temp___07EC11B9' is dependent on column 'Temp_Qty'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN Temp_Qty failed because one or more objects access this column.

When I add the new column it appears to automatically add the default constraint shown above (DF__tblSaleIt__Temp___07EC11B9)

I just add the column and then try to drop it as a test and it causes this error.

[Blade]

Basically, a default in sql server is a constraint and this is why it is getting created. This applies whether you add the column through Enterprise Manager or Query Analyser.
The difference comes when you want to drop the column. If you use EM, then it drops the constraint automatically, however if you use QA, then you'll have to manually drop the constraint before you can drop the column (as you are seeing!)

so, you just need to put:

ALTER TABLE Temp_QTY DROP CONSTRAINT [DF__tblSaleIt__Temp___07EC11B9]

before you can drop the column (or just drop it through EM!).

[nightshift]

thanks for that blade.

So, programmically, how would I know what the name of the constraint (that is automatically created) is?

If I know this then I can delete it...

Thank you in advance,

Nightshift

[Blade]

Difficult one tbh..

IMO you would probably be better off adding the column without the default and then add the default on manually so that you can give it a name. You then know the name of the default to drop.
For example:

'add the column with no default
ALTER TABLE [Pub_Info] ADD [TestCol] float NULL

'if you want it to populated then run an update query setting them all to the default value
UPDATE Pub_Info SET TestCol = 0

'now add on you default with any name you like
ALTER TABLE [Pub_Info] ADD CONSTRAINT [DF_PubInfo_TestCol_1] DEFAULT (0) FOR [TestCol]

'there are now no problems to drop the constraint because you know its name
ALTER TABLE [Pub_Info] DROP CONSTRAINT [DF_PubInfo_TestCol_1]

'and now the column can be dropped straight after
ALTER TABLE [Pub_Info] DROP COLUMN [TestCol]

If you wish to stick with the way you are adding the column then it gets a bit messy and cumbersome to find and remove the default. I did find this piece of sql which you can put into a stored procedure which will do it for you though: http://www.databasejournal.com/img/DropColumn.txt

Hope this helps


Just noticed, 5½ years here and this is my 100th post! Cracking ratio