Cannot drop primary key on table with index FULLTEXT
I have a table with a primary key which is datatype int
. I want to delete this column (since it is not used and I am afraid this column may reach the maximum data type limit int
, so we can delete it as well).
First, I couldn't refuse. I first tried to hide the constraint:
ALTER TABLE dbo.MyTable DROP CONSTRAINT PK_MyTableID
I am getting the error:
Cannot drop index 'PK_MyTableID' because it enforces the full-text key for table or indexed view 'MyTable'.
I don't understand this error because the primary key is this int
and I don't think this table has a FULLTEXT index, but if it does, I don't need it.
EDIT:
I was able to drop the column after dropping the FULLTEXT index:
DROP FULLTEXT INDEX ON dbo.MyTable
source to share
I believe there is a full text index on the table. A complete text index requires a unique key:
From MSDN : KEY INDEX index_name Is the name of the unique key index for table_name. KEY INDEX must be a unique, single key, non-nullable column. Choose the smallest unique key index for the full-text unique key. For best performance, it is recommended that you use an integer data type for the full-text key.
You can check the full text indexes of tables using:
SELECT object_id, property_list_id, stoplist_id FROM sys.fulltext_indexes
where object_id = object_id('myTable');
source to share