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

• sameer.kasi200x

  SSC Veteran

  Points: 276

  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.

• sameer.kasi200x

  SSC Veteran

  Points: 276

  More actions

  July 22, 2010 at 5:20 am

  #1196751

  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: 135369

  More actions

  July 22, 2010 at 5:36 am

  #1196757

  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

• sameer.kasi200x

  SSC Veteran

  Points: 276

  More actions

  July 22, 2010 at 6:15 am

  #1196780

  I am using SQL Server 2008 R2

  Product Version is 10.50.1600.1.

  Product Level: RTM

  Edition: Workgroup

  BuildClrVersion: v2.0.50727

• sameer.kasi200x

  SSC Veteran

  Points: 276

  More actions

  July 22, 2010 at 6:26 am

  #1196796

  Sorry to bother you guys...

  I tried with SQL Server Management Studio, and it worked as expected...

  It seems to be an issue with my IDE or jar file...

  Restarting my machine helped...