How do I get both FULLTEXT indexes and foreign key constraint indexes for the same table? InnoDB supports foreign key constraints but not FULLTEXT indexes, while MyISAM supports FULLTEXT but not foreign key constraints. How do I get both into one table? Is there another engine type that supports both abilities? Or does MySQL not allow these two capabilities in one table?
Thanks for taking the time to read this post. All help is appreciated.
[EDIT] From my research it seems as though I can't have both full text search and foreign key constraints in the same table. So what am I to do?
MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints
Moderator: General Moderators
Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints
Use sphinx for text search and InnoDB as the storage engine.
Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints
I'm on a shared server; is there any other solution, sense I can't install anything on my server?
Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints
Each storage engine has its own advantages and disadvantages. Decide what is more important to you and go with that. For small tables you can use the LIKE operator with acceptable performances.
Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints
There are more than just MyISAM and InnoDB table types. See if any of the other engines are available and will do what you want.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints
Not that I can find. I've look here and couldn't seem to find an engine that supports both full text search and foreign key constraints.pickle wrote:There are more than just MyISAM and InnoDB table types. See if any of the other engines are available and will do what you want.
Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints
One that isn't listed there is Maria - the successor to MyISAM, developed by the guy who invented MySQL. At a quick glance I didn't see anything about foreign keys, however.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints
You would think that if there was a database engine type that supported both foreign key constraints and full text search, that it would be a little more popular/well-known. I don't what engines my hosting provider provides, nor do I know how to find out (I might have to contact them). I've googled for full text and foreign keys in the same table but didn't find anything mentioning a specific engine type. Maybe I'll have to either give up foreign keys and code the restrictions or give up the full text search and create a keywords table of keyword indexes.pickle wrote:One that isn't listed there is Maria - the successor to MyISAM, developed by the guy who invented MySQL. At a quick glance I didn't see anything about foreign keys, however.