Mysql: adding foreign key does not give warning/er

Question

Here is a 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 try 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 errors! No warnings! Because of this reason, I have been using a internal database without foreign keys (assuming they were present) for some time now. Any idea if this is a bug or am I doing something wrong? Any workarounds or options in mysql that would avoid such pitfalls?

---

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

---

thanks

JP

Answer 1

myISAM doesn't do referential integrity. it seems it is legal to add such constraints and hence no error, though the constraints won't be enforced as long as the engine is myISAM. It does store the keys created so the user can see what was intended, though.

Answer 2

The InnoDB engine should generally (unless you have a fulltext index for instance) be preferred, as InnoDB allows transactions, foreign keys, and row locking, for instance.

On a test database, perform

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

(and any other relevant - maybe all - tables)

and ensure you do not have any side effects (See Mysql Alter Table).

Check also the parameters specific to InnoDB (in my.sql), See also the Mysql InnoDB engine.