469,623 Members | 1,179 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Drop Column problem

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__col2__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.
Jul 20 '05 #1
2 33064
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
--
Jul 20 '05 #2
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" <jl********@hotmail.com> wrote in message
news:bd**************************@posting.google.c om...
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__col2__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.

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Marlene harkcom | last post: by
1 post views Thread by Thomas Chille | last post: by
5 posts views Thread by Markus | last post: by
1 post views Thread by Gladiator | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.