SQL Server 2005 FullText index ''IX_on_an_identity_col is not a valid index to enforce a full-text search key...'
ok, got a problem creating a full text catalog.
This is the code I create my unique non nullable etc. index with
CREATE UNIQUE NONCLUSTERED INDEX [IX_unique] ON [dbo].[my_table]
(
[my_identity_column] ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90
) ON [PRIMARY]
The column it's created on is a non nullable, identity column storing ints.
Running this then fails
EXEC sp_fulltext_table
@tabname='my_table',
@action='create',
@ftcat = 'my_catalog',
@keyname = 'IX_unique'
GO
With this message
'IX_unique' is not a valid index to enforce a full-text search key. A full-text search key must be a uni开发者_开发知识库que, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, and has maximum size of 900 bytes. Choose another index for the full-text key.
Anyone got a clue? I've googled but the main mistake people seem to eb making is using the column name instead of the index name.
Thanks, Robin
==SOLVED==
Doh
You can't see it in the above as I tidied my sql to get rid of specific table names, at the same time I also removed the problem. The index was being created with a slightly different name from the index I was trying to reference when creating the Full Text index. Some indication of the actual problem from SQL Server would have been nice but I guess at the moment I'm in no great position to criticise another's work.
Guess I'll leave this as testament to my idiocy and so others can perhaps learn from it.
精彩评论