Drop Column problem

[James Knowlton]

Hello,

I'm just returning to MS SQL Server after two years of dealing with
Sybase ASE. I need to drop a column, using the alter table command.
I keep getting an error indicating that a constraint is using the
column. Here is the create script for the table
create table mytable
(col1 char(1) not null,
col2 char(1) default 'A' not null
)

Here is the alter table command:
alter table mytable drop column col2

When I run it I get the following error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__mytable__c ol2__114A936A' is dependent on column
'col2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col2 failed because one or more objects access
this column.

Is there anyway to tell the database to drop all column constraints
when the column is deleted?

Thanks,

James K.

[David Portas]

Re: Drop Column problem

Unfortunately you have to drop the default first. Give your default a
meaningful name so that it's easier to refer to it in other statements:

create table mytable
(col1 char(1) not null,
col2 char(1) constraint DF_mytable_col2 default 'A' not null
)

ALTER TABLE mytable DROP CONSTRAINT DF_mytable_col2
ALTER TABLE mytable DROP COLUMN col2

--
David Portas
SQL Server MVP
--

[John Bell]

Re: Drop Column problem

Hi

I don't think there is a way to easily do this, the syntax of the ALTER
table does not allow it without extra work.

You can drop the constraint before the column in the same statement, but to
do this you would need to know the name. As you haven't specified a name in
your create table statement the system generates one for you. It is
therefore easier to create the defaults in an alter table statement , you
can then specify the default constraint name.

create table mytable
(col1 char(1) not null,
col2 char(1) not null
)

ALTER TABLE mytable
ADD CONSTRAINT DF_mytable_col2 default 'A' FOR col2

alter table mytable
drop constraint DF_mytable_col2 ,
column col2

John
"James Knowlton" <jlknowlton@hot mail.com> wrote in message
news:bde3b38b.0 407010716.62a5b 53a@posting.goo gle.com...[color=blue]
> Hello,
>
> I'm just returning to MS SQL Server after two years of dealing with
> Sybase ASE. I need to drop a column, using the alter table command.
> I keep getting an error indicating that a constraint is using the
> column. Here is the create script for the table
> create table mytable
> (col1 char(1) not null,
> col2 char(1) default 'A' not null
> )
>
> Here is the alter table command:
> alter table mytable drop column col2
>
> When I run it I get the following error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__mytable__c ol2__114A936A' is dependent on column
> 'col2'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col2 failed because one or more objects access
> this column.
>
> Is there anyway to tell the database to drop all column constraints
> when the column is deleted?
>
> Thanks,
>
> James K.[/color]