removing table defaults

[chajadan]

Often times, when I create a table, I supply a default:

"create table thisone (field1 int default 0, field2 char(2) default 'ed')"

etc...

But at any point if I want to remove a field that I put a default on I get an error. Here's what I'd get if I tried to removed field2:

"The object 'DF__thisone__field2__100496D2' is dependent on column 'field2'."

I can't find anyway to remove this default. I tried everything, from sp_unbindefault to alter table drop constraint to trying to modify sysobjects directly (which I am not currently allowed to do)

Geez, I just want to remove the field--there must be an easy answer. If you can shed any light, I'd appreciate it!

--chajadan

 

[tlbroadbent]

Have you tried DROP DEFAULT before trying to ALTER the table?

DROP DEFAULT DF__thisone__field2__100496D2 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[chajadan]

Doesn't work.

I get this:

The default 'DF__thisone__field2__100496D2' cannot be dropped because it is bound to one or more column.

Every time I try to use sp_unbindefault I get an error message. I guess that would mean I'm not doing it right.

But so for now, I'm not much further.

Thanks for the tip

--chajadan

 

[harmmeijer]

DROP DEFAULT will not work you try to drop a DEFAULT object (like a rule), what you need to disable is the default constraint you made on the table.
The DEFAULT object is a default value wich you can bind to one or more tables.
The default constraint is like a primary key, this is part of the table's design.
Anyway to disable the default use:
alter table thisone
nocheck
constraint DF__thisone__field1__6A30C649

 

[harmmeijer]

Sorry, to permenently remove it you have to use:

alter table thisone
drop constraint DF__thisone__field1__6A30C649

 

[chajadan]

Thanks!!

This is the one that worked for me:

alter table thisone
drop constraint DF__thisone__field1__6A30C649

It's nice to finally be able to delete a little field :-D

--chajadan