MySQL ignores foreign key constraints

am 20.05.2011 13:07:22 von Mimi Cafe

Re: MySQL ignores foreign key constraints

am 20.05.2011 13:14:07 von Andrew Moore

--20cf304346d46c90bf04a3b338b6
Content-Type: text/plain; charset=ISO-8859-1

Try

show create table ... ;

A

On Fri, May 20, 2011 at 12:07 PM, Mimi Cafe 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
>

--20cf304346d46c90bf04a3b338b6--

Re: MySQL ignores foreign key constraints

am 20.05.2011 13:14:55 von sureshkumarilu

--20cf302efad841264804a3b33b64
Content-Type: text/plain; charset=ISO-8859-1

WHat are the table engine types ?

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

--20cf302efad841264804a3b33b64--

RE: MySQL ignores foreign key constraints

am 20.05.2011 14:49:52 von Mimi Cafe

------=_NextPart_000_0030_01CC16F4.CC31AA60
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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@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 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

------=_NextPart_000_0030_01CC16F4.CC31AA60--