SQL Server 2008 Full text search on a table with a composite primary key
I am trying to put full text search working on SQL Server 2008, however the table i am trying to index is a table with a composite primary key, something like this:
EXEC sp_fulltext_catalog 'My_Catalog', 'create'
EXEC sp_fulltext_table 'Message', 'create', 'My_Catalog', 'PK__MESSAGES__C87C0C9C0EC32C7A' // PK__MESSAGES__C87C0C9C1EC32C6A is a composite key
and i get the following error: "'PK__MESSAGES__C87C0C9C1EC32C6A' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key."
This means that i can't use the full text search in tables with composite primary keys? Or am i doing something wrong?
Thanks in advance
I think the error message is pretty clear, no?
" PK_MESSAGES_C87C0C9C1EC32C6A is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key."
If your index is not unique, not non-nullable, and not single-column, it cannot be used for fulltext indexing.
This means that i can't use the full text search in tables with composite primary keys? Or am i doing something wrong?
No, as the error says - this kind of index will not work. Period. No way around it.
As Remus pointed out - it doesn't ever say it has to be the primary key index! If you have some other non-nullable and unique field on the table, you can use a unique index on that field for your purposes. You could even just simply add a INT IDENTITY(1,1) field to your table and put a UNIQUE INDEX on that single field and you should be good to go.
A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes.
Note that it doesn't have to be the primary key (ie. the PRIMARY KEY constraint on the table). Any unique index on a non-nullable single column will work. If you don't have such a column, add an identity column to the table and add an index to it, then use this index for the full text catalog.
链接地址: http://www.djcxy.com/p/66850.html