SQL Server 2008 Full text search on a table with a composite primary key

Question

Hi everyone!

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

Answer 1

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.

Answer 2

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.