FULL-TEXT Search: Help Needed with the Unique Key Requirement
I am trying to create a full-text index on an table so that I can search it. The current primary key on the table contains two fields (Contract and LeaseName). I want to search LeaseName, but it is not unique by itself. I know that I can create a new field called ContractLeaseName which is a concatenation of the two fields (Contract and LeaseName) that are currently the primary key for the table. I can then use ContractLeaseName to then build the unique key for the full text search. This seems to me to be a potential waste of resources since I would have to maintain in my table the Contract, LeaseName, and the ContractLeaseName fields as well as modify my application to maintain all three fields for inserts and updates. Is this the only way to do this or is there something I still don't understand about Full-Text search?