470,624 Members | 2,276 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,624 developers. It's quick & easy.

Drop Default Constraints

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

Expand|Select|Wrap|Line Numbers
  1.  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

Expand|Select|Wrap|Line Numbers
  1.  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

Expand|Select|Wrap|Line Numbers
  1.  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)
Expand|Select|Wrap|Line Numbers
  1.  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
Oct 31 '07 #1
5 16892
debasisdas
8,127 Expert 4TB
Try to use

ALTER TABLE tablename DROP constraint constraintname

again the constraint can't be dropped if it has any dependency.
Oct 31 '07 #2
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:

Expand|Select|Wrap|Line Numbers
  1.  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
Expand|Select|Wrap|Line Numbers
  1.  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
Oct 31 '07 #3
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
Oct 31 '07 #4
alter table [Table Name] drop column [Column Name]
Jul 26 '12 #5
Rabbit
12,516 Expert Mod 8TB
@9710682106, your answer is wrong. They want to drop constrainst, not columns. Also, the thread is over 4 years old.
Jul 26 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by James Knowlton | last post: by
4 posts views Thread by Johan Vervloet | last post: by
1 post views Thread by tchangmian | last post: by
1 post views Thread by Igor Kryltsov | last post: by
6 posts views Thread by Giacomo | last post: by
2 posts views Thread by Eric Bragas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.