This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(99.2, 2%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Please help:
1- I have SQL Server 2017 installed with full-text search component. Please see the four attached snapshots regarding a testTable in a text database called myTestDB.
2- As you can see from the first snapshot (1-testTable.jpg), there is a testable with 3 columns including pk_id as a primary key.
3- The snapshot (2-CannotCreateFullTextIndex.jpg) shows that I created a default full-text search catalog on the myTestDB but my attempt to create a fulltext index on two columns (firstName, lastName) of the testTable failed.
4- The snapshot (3-FullTextSearchCreated.jpg) confirms that the full-text search has been created on the myTextDB.
5- Because I could not create the fulltext index, naturally I get the following message as shown the snapshot (4-CannotUseContain.jpg):
“Msg 7601, Level 16, State 2, Line 3
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'testTable' because it is not full-text indexed.“
6- Also, I am supposed to have the option of replacing “create fulltext index” with right clicking the table name and selecting the “fulltext option”. However, the latter is grayed out as shown in the snapshots.
Could an expert answer the following two questions please:
A- why when I right click the table name the “fulltext option” is grayed out although the fulltext search component is installed?
B- what is my mistake in creating the fulltext index shown in the snapshot (2-CannotCreateFullTextIndex.jpg) ?
I appreciate your help.
Your mistake is in the second screenshot. You are giving the name of the primary-key column. You should give the name of the primary key itself.
CREATE TABLE sometable (id int NOT NULL,
text varchar(MAX) NULL,
CONSTRAINT pk_sometable PRIMARY KEY (id)
)
go
CREATE FULLTEXT INDEX ON sometable (text) KEY INDEX pk_sometable
Hi there:
Thank you for answering my question. Although I marked your answer as an accepted answer, the problem persists when a table has composite primary keys. When I want to create a full-text index for a testTable with composite primary keys pk_id_1 and pk_id_2, I get the following error:
"Cannot use a contains or freetext predicate on table or view tableName because it is not full-text indexed.
I searched and some say by having a unique not null column the problem can be solved. I added a column named uniqueCol of type int with identity 1:1 and the problem persists. When I right click the table name to choose the option "full-text index", after clicking next button, I get error message "No unique index available."
Do you or someone else know how can I create the full-text index on the testTable when it has composite primary keys pk_id_1 and pk_id_2?
Thank you
The index you specify with the KEY INDEX clause must be a unique one-column index. That UniqueCol column should suffice - but you need to create an index on it.