Why my table doesnt support FOREIGN KEYS?

Question

I have made a database and 10 tables in mysql(table type NONE) and i can't create foreign keys. An alert message says

SQLyog The selected table does not support foreign keys. Table engine must be InnoDB, PBXT or SolidDB

Will i must alter all of my tables now? thanks for any hint or suggestion

Answer 1

Yes. The default storage engine (MyISAM) does not support foreign key constraints.

Answer 2

The engine you're using doesn't support foreign keys (as the error message tells you obviously). You can change the engine you're using with

ALTER TABLE `tableName` ENGINE = newEngine;

Answer 3

Typically, InnoDB is used to support foreign keys, and transactions:

http://dev.mysql.com/doc/refman/5.1/en/innodb.html

You can change the table engine on the fly:

ALTER TABLE tableName ENGINE = InnoDB;

You can also change the default -- for newly created tables -- as shown here:

http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html

Answer 4

The MyISAM engine DOES support foreign keys. What it doesn't support is foreign key constraints.

You can go ahead and create as many foreign keys as you like - but don't expect the database to enforce integrity for you.

Don't change engines in your application without significant testing; changing engine will affect behaviour and is not recommended without great care and a lot of QA work. In particular, switching from MyISAM to InnoDB will break your application if:

• It doesn't handle deadlocks appropriately (Deadlocks do not happen on MyISAM)
• It expects table-level locking
• It relies on (mostly dubious) MyISAM features
• It is high performance and you have not understood how to tune InnoDB correctly, but did tune MyISAM well (or ok)
• You have not assessed the disc space impact of switching engines

Answer 5

thanks for all the answers. So the only way to use foreign key constraints that the database will protect me from unwanted entries is changing from MYISAM to InnoDB?

For example i have 2 tables reservations [id] transactions [id,resv_id(foreign key to reservations table)]

InnoDB will protect me from inserting a record in transactions table with resv_id that don't have an entry in reservations table?