-1

I am creating a MySQL DB with MyISAM engine. I believe it is not possible to use foreign key with MyISAM. All the tables in DB have primary key, but all of them also need at least 1 FK.

1)Can any of the other options available (apart from PK): such as: UNIQUE, INDEX FULLTEXT or SPATIAL be used instead of FK that serves the same purpose?

2)If the answer to 1) is <0, what else can be done (except changing to InnoDB)?

peacefull warrior
  • 9
  • 7

3 Answers3

2

It smells like you don't understand the purpose of a FOREIGN KEY. It is for one thing:

  • Referential integrity. But this is not a requirement, it is a feature that you can live without if you write good code.

A FK has a side effect: It builds an INDEX. But the only purpose of an index is:

  • Faster lookups. This includes WHERE clauses and JOINs between tables.

It is a somewhat common misconception that you need a FK to JOIN two tables. That is totally false. Nor is an INDEX required.

Bottom line...

  1. Get the schema written and your queries written.
  2. When you hit performance problems (which might happen when you have a few thousand rows in a table), look into adding INDEX(es).

Regardless, move to InnoDB.

Rick James
  • 135,179
  • 13
  • 127
  • 222
1

No, MyISAM does not support FK constraints. You can't enforce referential integrity in MyISAM. You could code it yourself using triggers, but this is not recommended.

You can create an index, either unique or non-unique, to provide query optimization for searching or sorting. But this does not provide referential integrity.

ALTER TABLE MyTable ADD INDEX (column1, column2);

or

ALTER TABLE MyTable ADD UNIQUE INDEX (column1, column2);

Don't use a UNIQUE index unless you want the columns to have a unique constraint in addition to the index.

Don't use FULLTEXT or SPATIAL index unless you mean to do fulltext or spatial queries. Those indexes are for those special purposes, and in general they are not interchangeable with ordinary indexes.

And for the record, you should be using InnoDB.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

The answer to your question is no, there is no real alternative in MyISAM.

Depending on your version of MySQL. You could look at before triggers but you would have to write a trigger in place of each foreign key that you would normally create under Innodb. This isn't recommended though, you're reinventing the wheel by doing this and could encounter problems if the logic in the trigger is incorrect. Defeating the purpose.

Unless there's a feature of MyISAM that you can't get with Innodb, I would highly recommend using Innodb and optimizing where necessary.

Hope that helps.

flip
  • 555
  • 3
  • 8