List:General Discussion« Previous MessageNext Message »
From:Mimi Cafe Date:May 20 2011 12:49pm
Subject:RE: MySQL ignores foreign key constraints
View as plain text  
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:sureshkumarilu@stripped] 
Sent: 20 May 2011 12:15
To: Mimi Cafe
Cc: mysql@stripped
Subject: Re: MySQL ignores foreign key constraints

 

WHat are the table engine types ?

On Fri, May 20, 2011 at 4:37 PM, Mimi Cafe <mimicafe@stripped> 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


Thread
MySQL ignores foreign key constraintsMimi Cafe20 May
  • Re: MySQL ignores foreign key constraintsAndrew Moore20 May
  • Re: MySQL ignores foreign key constraintsSuresh Kuna20 May
    • RE: MySQL ignores foreign key constraintsMimi Cafe20 May