'Can I use a composite primary key as the index in a free text index?
I am trying to create a FREE TEXT INDEX for the first time. This is what I have:
create table Person (
ID int identity not null,
[type] int not null,
Notes nvarchar(1000),
CONSTRAINT [PersonPK] PRIMARY KEY CLUSTERED (ID,[type])
)
insert into Person ([type], Notes) values (1, 'Attended a football match')
insert into Person ([type], Notes) values (1, 'Went to the cinema')
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON Person(Notes)
KEY INDEX PersonPK
WITH STOPLIST = SYSTEM;
The error I see is:
Msg 7653, Level 16, State 2, Line 23
'PersonPK' 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.
I believe this is because I am using a composite primary key. This runs:
create table Person (ID int identity not null, [type] int not null, Notes nvarchar(1000), CONSTRAINT [PersonPK] PRIMARY KEY CLUSTERED (ID ASC))
insert into Person ([type], Notes) values (1, 'Attended a football match')
insert into Person ([type], Notes) values (1, 'Went to the cinema')
CREATE UNIQUE INDEX PersonIndex ON Person(ID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON Person(Notes)
KEY INDEX PersonIndex
WITH STOPLIST = SYSTEM;
and then this works as expected i.e. returns one row.
SELECT *
FROM Person
WHERE CONTAINS(Notes,'football') ;
The problem is that the Person table contains a composite primary key. Entity.ID is unique so I believe my solution will work, however can I use a composite primary key as the index? I am using SQL Server 2019.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
