1) Sorry for not giving you the version:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
*mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.17-nt |
+-----------+*
1 row in set (0.00 sec)
2) Further, please note that the following output, of course, shows the
outlook of the table `relation` after the FOREGIN KEY definitions:
mysql> SHOW CREATE TABLE relation\G
*************************** 1. row ***************************
Table: relation
Create Table: CREATE TABLE `relation` (
`id` smallint(5) unsigned NOT NULL,
`idx` smallint(5) unsigned NOT NULL,
* KEY `idx` (`idx`),
KEY `id` (`id`),
CONSTRAINT `relation_ibfk_1` FOREIGN KEY (`idx`) REFERENCES `friends`
(`id`),
CONSTRAINT `relation_ibfk_2` FOREIGN KEY (`id`) REFERENCES `friends`
(`id`)
*) *ENGINE=InnoDB* DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Defining an index on the category column of the `friends` table makes no
difference.
mysql> SHOW TABLES;
+--------------------+
| Tables_in_kontakte |
+--------------------+
| category |
| friends |
| relation |
+--------------------+
3 rows in set (0.00 sec)
*mysql> ALTER TABLE friends ADD INDEX (category);
Query OK, 1 row affected (0.27 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES
category(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint f
ails (`kontakte/#sql-714_7`, CONSTRAINT `#sql-714_7_ibfk_1` FOREIGN KEY
(`catego
ry`) REFERENCES `category` (`id`))*
mysql> SHOW CREATE TABLE friends\G
*************************** 1. row ***************************
Table: friends
Create Table: CREATE TABLE `friends` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`firstname` varchar(22) collate latin1_german2_ci default NULL,
`lastname` varchar(30) collate latin1_german2_ci default NULL,
`nick` varchar(20) collate latin1_german2_ci default NULL,
`birthdate` date default NULL,
`gender` enum('m','f') collate latin1_german2_ci default 'm',
`category` smallint(5) unsigned NOT NULL,
`phonehome` varchar(30) collate latin1_german2_ci default NULL,
`phonework` varchar(30) collate latin1_german2_ci default NULL,
`phonemobile` varchar(30) collate latin1_german2_ci default NULL,
`email` varchar(38) collate latin1_german2_ci default NULL,
`street` varchar(38) collate latin1_german2_ci default NULL,
`town` varchar(28) collate latin1_german2_ci default NULL,
`zip` smallint(5) unsigned default NULL,
`country` char(3) collate latin1_german2_ci default NULL,
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `Name` (`lastname`,`firstname`),
* KEY `category` (`category`)
*) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
1 row in set (0.01 sec)
InnoDB Status after failing instruction:
*mysql> SHOW INNODB STATUS;*
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060312 13:40:40 Transaction:
TRANSACTION 0 7440, ACTIVE 0 sec, OS thread id 3048 inserting, thread
declared
nside InnoDB 499
mysql tables in use 2, locked 2
6 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 7, query id 29 localhost 127.0.0.1 root copy to tmp table
ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES category(id)
Foreign key constraint fails for table `kontakte/#sql-714_7`:
,
CONSTRAINT `#sql-714_7_ibfk_1` FOREIGN KEY (`category`) REFERENCES
`category`
(`id`)
*Trying to add in child table, in index `category` tuple:
DATA TUPLE: 2 fields;
0: len 2; hex 0001; asc ;; 1: len 2; hex 0001; asc ;;
*
But in parent table `kontakte/category`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 696e66696d756d00; asc infimum ;;
The further error message from InnoDB status is again quite meaningless
and seems to make no sense. I have not found anything on the forums to
the question. This is somehow amazing, since it' s a quite simple
constellation that I am trying to establish here. Does someone really
understand the InnoDB error message and what's going wrong? It is not a
name clash either, for I tried giving the `category` column another name.
Andy Krueger
Martijn Tonies wrote:
Hello Andreas,
You're not telling us what version of MySQL you're using,
so I'll have to give it a shot in the dark ...
mysql> SHOW CREATE TABLE friends\G
*************************** 1. row ***************************
Table: friends
Create Table: CREATE TABLE `friends` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`firstname` varchar(22) collate latin1_german2_ci default NULL,
`lastname` varchar(30) collate latin1_german2_ci default NULL,
`nick` varchar(20) collate latin1_german2_ci default NULL,
`birthdate` date default NULL,
`gender` enum('m','f') collate latin1_german2_ci default 'm',
`category` smallint(5) unsigned default '0',
----> create an index on the CATEGORY column and try again.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
