Drop Default Constraints

[sreemati]

Hi

I am working on SQL SERVER 200 and I am trying to drop the default constraints set in few tables. I tired to follow the instructions given in MSDN for dropping a default:

1) Unbind the code

 Exec  sp_unbindefault 'tablename.columname' 

When I try to run it, it gives me following this error message:

Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'tablename.columname'. Use ALTER TABLE DROP CONSTRAINT.

2) AS ore the second step mentioned in MSDN and from the error message, I tired Alter table drop constraint

 ALTER TABLE tablename DROP DEFAULT [constraintname] 

Again an error message: Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DEFAULT'.

3) So I try only to use drop default constraint

 DROP DEFAULT constraintname 

Third error message: Server: Msg 3716, Level 16, State 3, Line 1
The default 'constraintname' cannot be dropped because it is bound to one or more column.

Finally I read few more tutorial, though almost all said the same, I was inclined to try another syntax mentioned in one of them:

4)

 ALTER TABLE tablename ALTER COLUMN columname DROP DEFAULT constraintname 

This again gave me another error message as copied below: Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DEFAULT'.

So finally I have run out of ideas and would be glad if anyone can be of some help. I am sure someone should have come across this issue and maybe found a work around.

Thanks a ton in advance :)
Sree

[debasisdas]

Try to use

ALTER TABLE tablename DROP constraint constraintname

again the constraint can't be dropped if it has any dependency.

[sreemati]

Try to use

ALTER TABLE tablename DROP constraint constraintname

again the constraint can't be dropped if it has any dependency.

Hi debasisdas,

Thanks for your quick response but thats what I have tried in code 2) I have listed in my original post. I thought you meant me to try:

 ALTER TABLE tablename DROP Constraint [constraintname]  

I got the following error message: Server: Msg 3728, Level 16, State 1, Line 1
'DF_Population_History_PPATIENTS' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 1
Could not drop constraint. See previous errors.

Than I tired this again

 DROP DEFAULT constraintname 

And surprisingly got this message: Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the default 'DF_Population_History_PPATIENTS', because it does not exist in the system catalog.


Than I checked the table and the constraint was gone, now I am not sure which one worked. I will try in few more and post it back.

Thanks
Sree

[sreemati]

Hi Debasis,

Your code worked, I mean the DROP Constraint Constraintname, thought its still giving me the error message, when I actually go back and check the table, the constraint is gone.

I more of Idiosyncrasies of SQL Server 2000, hail MS ;)

Anyways, thanks a lot, it works even though it gives error message though I will glad to know why it acts like this.

Cheers
Sree

[9710682106]

alter table [Table Name] drop column [Column Name]

[Rabbit]

@9710682106, your answer is wrong. They want to drop constrainst, not columns. Also, the thread is over 4 years old.