0

I am trying to move a field do a different position in the table (I'm just trying to get it in alphabetical order). When I click save, I get this error message (TaskID is the name of another field):

'tblTask' table
- Unable to create index 'idx_TaskID_notnull'.  
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tblTask' and the index name 'idx_TaskID_notnull'. The duplicate key value is (<NULL>).
The statement has been terminated.

I have one unique non-clustered index for TaskID that has the filter TaskID IS NOT NULL.

Why am I getting this error?

Mike
  • 961
  • 6
  • 19
  • 43

1 Answers1

0

You're only allowed one of any value in a unique index, include NULL so if you have multiple rows with NULL values, that's why you would get the error.

I presume, because you say "brand new field" that you've just created the column. If you did so without also populating it, all of the values would be NULL.

The simplest way to take care of this is to:

  1. Populate the appropriate value for the column, or at least whatever you want your default value to be if you don't know the appropriate one to start.

  2. ALTER your column so that it is NOT NULL

    alter table dbo.tbltask alter column taskid int not null

  3. Add a default constraint to your column so that it always has a value.

    alter table tbltask add constraint df_taskid default (0) for taskid

Alternatively, if you want all old rows in your table to be NULL and only new rows always be NOT NULL you can add a filtered index instead, since you're working with 2008 (or higher).

create unique nonclustered index idx_TaskID_notnull on dbo.tbltask (taskid) 
    where taskid is not null
Jason Whitish
  • 1,428
  • 1
  • 23
  • 27
  • I've filtered the index for non-null values, so it should allow nulls, right? Also the new field I am trying to allow NULLs in is a completely different field from the one in the error message. The new field is not included in any indexes and I have already populated it with data and set its default to a non-null value, not that I think that should matter since the error only mentions the TaskID field... I must be able to allow NULL values in TaskID while preventing duplicate non-NULL values. – Mike Jan 28 '16 at 18:59
  • @Mike Can you provide the definition for your index `idx_TaskID_notnull` in the question? That may help a good deal to resolve the difference. Your error is saying that you have a duplicate value in a key of the named index; would be good to look at what all are keys in the index. – Jason Whitish Jan 28 '16 at 20:11
  • Where do I go to find the definitions? SQL Server Management Studio seems to hide all SQL from me except in views... Though I can tell you TaskID is the only indexed key column there with no included columns. And the filter is `([TaskID] IS NOT NULL)` – Mike Jan 28 '16 at 22:59
  • @Mike That's a concern; you might not have permission. You can run this query to confirm: `SELECT TableName = t.name, IndexName = ind.name, IndexId = ind.index_id, ColumnId = ic.index_column_id, ColumnName = col.name, ind.*, ic.*, col.* FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE ind.name = 'name` – Jason Whitish Jan 29 '16 at 15:16
  • For completeness, that query above came from here: http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db – Jason Whitish Jan 29 '16 at 16:09