471,863 Members | 912 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,863 software developers and data experts.

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 33222
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
NeoPa
reply views Thread by NeoPa | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.