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:
我试图在SQL Server 2008上进行全文搜索,但是我想要索引的表是一个带有复合主键的表,如下所示:
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."
我得到以下错误:“'PK__MESSAGES__C87C0C9C1EC32C6A'不是强制执行全文搜索键的有效索引。全文搜索键必须是唯一的,不可为空的单列索引,它不是脱机的,是没有在非确定性或不精确的非持久计算列上定义,没有过滤器,最大大小为900字节。为全文密钥选择另一个索引。“
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
提前致谢
2 个解决方案
#1
8
I think the error message is pretty clear, no?
我认为错误信息非常清楚,不是吗?
"
PK_MESSAGES_C87C0C9C1EC32C6Ais 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."“PK_MESSAGES_C87C0C9C1EC32C6A不是强制执行全文搜索密钥的有效索引。全文搜索密钥必须是唯一的,不可为空的单列索引,该索引不是脱机的,不是在非确定性或不精确的上定义的非持久计算列,没有过滤器,最大大小为900字节。为全文键选择另一个索引。“
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.
正如Remus指出的那样 - 它永远不会说它必须是主要的索引!如果表中有其他非可空和唯一字段,则可以在该字段上使用唯一索引。你甚至可以简单地在你的表中添加一个INT IDENTITY(1,1)字段,并在该单个字段上放置一个UNIQUE INDEX,你应该很好。
#2
4
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.
全文搜索键必须是唯一的,不可为空的单列索引,该索引不脱机,未在非确定性或不精确的非持久计算列上定义,没有过滤器,最大大小为900字节。
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.
请注意,它不必是主键(即表上的PRIMARY KEY约束)。非可空单列上的任何唯一索引都可以使用。如果您没有这样的列,请向表中添加标识列并为其添加索引,然后将此索引用于全文目录。
#1
8
I think the error message is pretty clear, no?
我认为错误信息非常清楚,不是吗?
"
PK_MESSAGES_C87C0C9C1EC32C6Ais 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."“PK_MESSAGES_C87C0C9C1EC32C6A不是强制执行全文搜索密钥的有效索引。全文搜索密钥必须是唯一的,不可为空的单列索引,该索引不是脱机的,不是在非确定性或不精确的上定义的非持久计算列,没有过滤器,最大大小为900字节。为全文键选择另一个索引。“
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.
正如Remus指出的那样 - 它永远不会说它必须是主要的索引!如果表中有其他非可空和唯一字段,则可以在该字段上使用唯一索引。你甚至可以简单地在你的表中添加一个INT IDENTITY(1,1)字段,并在该单个字段上放置一个UNIQUE INDEX,你应该很好。
#2
4
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.
全文搜索键必须是唯一的,不可为空的单列索引,该索引不脱机,未在非确定性或不精确的非持久计算列上定义,没有过滤器,最大大小为900字节。
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.
请注意,它不必是主键(即表上的PRIMARY KEY约束)。非可空单列上的任何唯一索引都可以使用。如果您没有这样的列,请向表中添加标识列并为其添加索引,然后将此索引用于全文目录。