MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Post by JellyFish »

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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Post by Eran »

Use sphinx for text search and InnoDB as the storage engine.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Post by JellyFish »

I'm on a shared server; is there any other solution, sense I can't install anything on my server?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Post by Eran »

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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Post by pickle »

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.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Post by JellyFish »

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.
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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Post by pickle »

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.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Post by JellyFish »

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.
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.
Post Reply