MySQL ignores foreign key constraints
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
Re: MySQL ignores foreign key constraints
Try show create table ... ; A On Fri, May 20, 2011 at 12:07 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
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
RE: MySQL ignores foreign key constraints
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
