List:General Discussion« Previous MessageNext Message »
From:Andreas Krüger Date:March 12 2006 12:50pm
Subject:SQL Foreign Key
View as plain text  
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
>  
>


Thread
SQL Foreign KeyAndreas Krüger12 Mar
  • Re: SQL Foreign KeyMichael Stassen12 Mar
    • Re: SQL Foreign KeyAndreas Krüger12 Mar