Andreas Krüger wrote:
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)
<snip>
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
The foreign key cannot be created because there is at least one row in friends
with a value for category which does not exist as an id in table category. In
general, you cannot create a constraint that is already violated. Try this:
SELECT f.id, f.category
FROM friends f
LEFT JOIN category c ON f.category=c.id
WHERE c.id IS NULL;
You will get a list of problem rows in table friends. Either change them to
point to existing categories, or add matching records to table categories. Then
you should be able to add the foreign key.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
