Aha, got the offender. Unlike all other ones, tables book_author was MyISAM instead of Innodb.
Now everything works alter table book_author add foreign key (fkauthor_id) references author (pkauthor_id); Query OK, 12 rows affected (0.39 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> alter table book_author add foreign key (fkisbn) references book (pkisbn); Query OK, 12 rows affected (0.42 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> show create table book_author; +-------------+------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- -----------------------------------+ | Table | Create Table | +-------------+------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- -----------------------------------+ | book_author | CREATE TABLE `book_author` ( `fkauthor_id` mediumint(10) unsigned NOT NULL, `fkisbn` varchar(20) NOT NULL, KEY `fkisbn` (`fkisbn`), KEY `fkauthor_id` (`fkauthor_id`), CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`fkisbn`) REFERENCES `book` (`pkisbn`), CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`fkauthor_id`) REFERENCES `author` (`pkauthor_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------------+-------------------------- Thanks From: Suresh Kuna [mailto:sureshkumar...@gmail.com] Sent: 20 May 2011 12:15 To: Mimi Cafe Cc: mysql@lists.mysql.com Subject: Re: MySQL ignores foreign key constraints WHat are the table engine types ? On Fri, May 20, 2011 at 4:37 PM, Mimi Cafe <mimic...@googlemail.com> wrote: Hi An ideas why MySQL silently ignores any foreign key constraints I define for the following tables? mysql> desc book; +------------------+-------------------------------+------+-----+---------+- ------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------------------------+------+-----+---------+- ------+ | pkisbn | varchar(20) | NO | PRI | NULL | | | fkpublisher_id | tinyint(3) unsigned | NO | MUL | NULL | | | title | varchar(50) | NO | | NULL | | | subtitle | varchar(50) | NO | | NULL | | 13 rows in set (0.01 sec) mysql> desc book_author; +-------------+------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------------+------+-----+---------+-------+ | fkauthor_id | mediumint(10) unsigned | NO | MUL | NULL | | | fkisbn | varchar(20) | NO | MUL | NULL | | +-------------+------------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc author; +-------------+------------------------+------+-----+---------+------------- ---+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------------+------+-----+---------+------------- ---+ | pkauthor_id | mediumint(10) unsigned | NO | PRI | NULL | auto_increment | | fname | varchar(20) | NO | | NULL | | | initial | varchar(5) | YES | | NULL | | | lname | varchar(20) | NO | | NULL | | +-------------+------------------------+------+-----+---------+------------- ---+ 4 rows in set (0.00 sec) Mimi -- Thanks Suresh Kuna MySQL DBA
