Dropping column with dependent constraints fails, but it drops the table

Question

Post reply

Dropping column with dependent constraints fails, but it drops the table

sameer.kasi200x

SSC Veteran

Points: 246
More actions
July 22, 2010 at 5:15 am

#221511

drop table dbo.tab1;

create table dbo.tab1 (col1 char(20) NOT NULL default 'E', col2 varchar(20), constraint pk_tab1 primary key(col1) );

alter table dbo.tab1 drop constraint pk_tab1;

ALTER TABLE dbo.tab1 NOCHECK CONSTRAINT ALL;

exec sp_rename 'dbo.tab1.col1','col3','COLUMN'

alter table dbo.tab1 drop column col3

The last statement fails stating a reason that a DEFUALT object is found dependent on tab1.col1.

But immideately after execution of this statement the table is no more available, it gets dropped. Is this expected behavior or I am doing something wrong some where or this is bug? Can some one please comment.

I tested and found even the following statements have same effect.

create table dbo.tab1 (col1 char(20) NOT NULL default 'E', col2 varchar(20), constraint pk_tab1 primary key(col1) );

alter table dbo.tab1 drop column col3;

Drop column gives the following error:

An error occurred when executing the SQL command:

alter table dbo.tab1 drop column col1

The object 'DF__tab1__col1__1EF03DF9' is dependent on column 'col1'. [SQL State=S0001, DB Errorcode=5074]

I am using SQL Workbench build 105 (on jre 1.6) for executing these TSQL queries.

I am using Microsoft SQL Server 2008 R2 on windows XP SP3.

Please let me know if more details are required.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sameer.kasi200x SSC Veteran Points: 246 More actions · Answer 1

I tested and found even the following statements have same effect.

create table dbo.tab1 (col1 char(20) NOT NULL default 'E', col2 varchar(20), constraint pk_tab1 primary key(col1) );

alter table dbo.tab1 drop column col1;

Where as the following commands completes successfully but does not drop the table:

create table dbo.tab1 (col1 char(20) NOT NULL default 'E', col2 varchar(20), constraint pk_tab1 primary key(col1) );

alter table dbo.tab1 drop column col2;

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

I got the same error message, but the table doesn't get dropped.

What's your product version info ?

(I tested on 10.50.1702.0 and 10.50.1720.0)

Select Serverproperty( 'BuildClrVersion' ) as BuildClrVersion



 , Serverproperty( 'Edition' ) as Edition

 , Serverproperty( 'EngineEdition' ) as EngineEdition

 , Serverproperty( 'InstanceName' ) as InstanceName

 , Serverproperty( 'IsClustered' ) as IsClustered

 , Serverproperty( 'ProductVersion' ) as ProductVersion

 , Serverproperty( 'ProductLevel' ) as ProductLevel

 , Serverproperty( 'ResourceLastUpdateDateTime' ) as ResourceLastUpdateDateTime

 , Serverproperty( 'ResourceVersion' ) as ResourceVersion

, Serverproperty( 'ServerName' ) as ServerName

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me