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

  • 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.

  • 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;

  • 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

  • I am using SQL Server 2008 R2

    Product Version is 10.50.1600.1.

    Product Level: RTM

    Edition: Workgroup

    BuildClrVersion: v2.0.50727

  • 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...

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

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