Cannot alter column when index exist

[kelon]

Hi all,

I'm trying to change the "Allow Nulls" property of a field to NOT NULL using

ALTER TABLE tablename ALTER COLUMN fieldname varchar(7) NOT NULL

But since there's an index that's dependent on the field, it returns with an ERROR. However, when I try to do this manually through the Design Table, I can check off that property.

Anybody know how I can make this work without going through the Enterprise Manager? Thanks!

PS- This is in SQL 2000

 

[tlbroadbent]

What is the Error message? Do any rows in the table contain a NULL in that column? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[kelon]

The error message basically said that it can't perform the operation because an index was dependent on the field. The table was clear when I tried running the operation.

I actually got around this by dropping the index and then recreating the index after the alter column. Kinda cheesy though.

 

[tlbroadbent]

Were you also changing the column size or data type? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.