can't alter column to NOT NULL

If I run the following 3 statements (on sql server 2000):
create table foo (x nvarchar(128))
create unique index ix_foo on foo (x)
alter table foo alter column x nvarchar(128) not null
I get this error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'ix_foo' is dependent on column 'x'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN x failed because one or more objects access this
column.
Looking in the documentation it says "The altered column cannot be...Used in
an index, unless the column is a varchar, nvarchar, or varbinary data type,
the data type is not changed, and the new size is equal to or larger than
the old size."
Since the column is nvarchar, the data type is not changed, and the new size
is equal to the old size, this should be allowed.
Is the documentation just wrong or have I misunderstood something?
AndyAndy Fish wrote:

> Since the column is nvarchar, the data type is not changed, and the
> new size is equal to the old size, this should be allowed.
> Is the documentation just wrong or have I misunderstood something?
You need to drop the index, alter the column and then recreate the
index, that's the only way.
HTH,
Stijn Verrept.|||Disable the foreign key constraints / drop them. Alter the table (make
sure there are no NULL values in there). Activate recreate the foreign
key relationship.
HTH, Jens Suessmeyer.sql

Cant add bit column to unique index

This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.

Can anyone shed any light on the problem?

Thanks.

You can not create index on BIT data type and that's the reason you can not see column.|||

Where can I find documentation on that? Why is that restriction in place?

|||

JigneshP, you definitely can create a unique index using bit fields. I was able to do it via TSQL in Query Analyzer. Here's the sql I used:

DROP INDEX [dbo].[Material].IX_MaterialCREATE UNIQUE INDEX [IX_Material]ON [dbo].[Material] ([MaterialID], [MyBitField])ON [PRIMARY]GO

The bit field is MyBitField. I then verified the index works by inserting data that duplicated another row except for the bit field.

I'm still looking for someone to tell me why I can't add the bit column to an index via Enterprise Manager.

|||

I found this link (http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html) that shows how to do it via Enterprise Manager. You have to do it from the Tasks menu / Manage Index.

|||Oh Thanks ZLA. Sorry about that.