Hi I am getting a Foreign key error .
The command that gives the error is
ALTER TABLE child ADD CONSTRAINT child_parent_fk FOREIGN KEY (id) REFERENCES
parent(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
And the error message is
ERROR 1005 (HY000): Can't create table
'./testforeignkeysyntax_lap/#sql-1515_130f.frm' (errno: 150)
I have attached the create table syntax for both the parent and child tables
and the innodb status below. I am quite a newbie and want to know what I am
doing wrong.
My mysql version is mysql Ver 14.12 Distrib 5.0.51b, for
apple-darwin9.0.0b5 (i686) using readline 5.0
Thanks for your help
Hari
mysql> show create TABLE parent;
+--------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+-------------------------------------------------------------------------------------------------------------------------------------+
| parent | CREATE TABLE `parent` (
`id` varchar(128) default NULL,
`name` varchar(256) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create TABLE child;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
`toy` varchar(64) default NULL,
`id` varchar(128) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE child ADD CONSTRAINT child_parent_fk FOREIGN KEY (id)
REFERENCES parent(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
ERROR 1005 (HY000): Can't create table
'./testforeignkeysyntax_lap/#sql-1515_130f.frm' (errno: 150)
mysql> show innodb status;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Status
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=====================================
110222 12:55:47 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 53 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 45, signal count 45
Mutex spin waits 0, rounds 40, OS waits 2
RW-shared spins 84, OS waits 42; RW-excl spins 1, OS waits 1
------------------------
LATEST FOREIGN KEY ERROR
------------------------
110222 12:54:53 Error in foreign key constraint of table
bioscreencast_lap/#sql-1515_130f:
FOREIGN KEY (id) REFERENCES parent(id) ON DELETE NO ACTION ON UPDATE NO
ACTION:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 15987
Purge done for trx's n:o < 0 15982 undo n:o < 0 0
History list length 3
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 2957864960
MySQL thread id 4879, query id 6565 localhost root
show innodb status
---TRANSACTION 0 15877, not started, OS thread id 2955325440
MySQL thread id 4859, query id 6230 localhost root
---TRANSACTION 0 0, not started, OS thread id 2957660160
MySQL thread id 4870, query id 6176 localhost root
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
54 OS file reads, 433 OS file writes, 183 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.17 writes/s, 0.06 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 3789251
Log flushed up to 0 3789251
Last checkpoint at 0 3789251
0 pending log writes, 0 pending chkp writes
96 log i/o's done, 0.02 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17436514; in additional pool allocated 982784
Buffer pool size 512
Free buffers 446
Database pages 65
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 60, created 5, written 491
0.00 reads/s, 0.00 creates/s, 0.23 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 2957045760, state: waiting for server activity
Number of rows inserted 0, updated 1, deleted 0, read 22199
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
