Thanks Michael, that' s the problem. Syntactically my attempt was
correct. Only I had the *value '1' in the only row that table
`friends`contains, whereas table `category`was still empty*. (I was
about creating the database for sample purposes.) When I tried to create
the FOREIGN KEY out of friends(category) referencing category(id), MySQL
detected that the value '1' is not contained in `category`.
In other words, MySQL didn' t allow the definition for the integrity of
the relational database.
Adding a single row (with automatic id value '1') to `category` allows
for the definition of the FOREIGN KEY allright:
*mysql> SELECT f.id, f.category
-> FROM friends f
-> LEFT JOIN category c ON f.category=c.id
-> WHERE c.id IS NULL;
+----+----------+
| id | category |
+----+----------+
| 1 | 1 |
+----+----------+
1 row in set (0.09 sec)
mysql> INSERT INTO category (name) VALUES ("School friends");
Query OK, 1 row affected (0.03 sec)
mysql> ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES
category(id);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0
*mysql>
Sincerely,
Andy Krueger
Michael Stassen wrote:
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
