Mysql: adding a foreign key does not give warning / error in MyISAM tables

Question

Here is the table I made:

mysql> show create table notes; +-------+----------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------+ | notes | CREATE TABLE `notes` ( `id` int(11) NOT NULL auto_increment, `note` text NOT NULL, `status` enum('active','hidden','deleted','followup','starred') default NULL, `created` datetime NOT NULL, `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+----------------------------------------+ 

I am trying to add a foreign key constraint:

 mysql> alter table notes add constraint foreign key(`id`) references `notetypes`.`id` on update cascade on delete restrict; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 

No mistakes! No warnings! For this reason, I have been using an internal database for some time without foreign keys (provided that they are present). Any idea if this is a mistake, or am I doing something wrong? Any workarounds or options in mysql that would avoid such errors?

---

 $ mysql --version mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2 

---

thanks

In JP

Answer 1

myISAM does not perform referential integrity. it seems legitimate to add such restrictions and therefore no errors, although the restrictions will not be respected as long as the engine is myISAM. It stores the created keys so that the user can see what was intended.

Answer 2

It is assumed that the InnoDB engine is usually (unless you have a full-text index), since InnoDB allows, for example, transactions, foreign keys, and row locking.

In the test database, run

  ALTER TABLE notes ENGINE = InnoDB; ALTER TABLE notetypes ENGINE = InnoDB; 

(and any other relevant - possibly all tables)

and make sure you have no side effects (see Mysql's answer table ).

Check also InnoDB-specific parameters (in my.sql ), see also Mysql InnoDB engine .