Anyone know how to get around this problem?
I can add one foreign key and constraint, but when I try to add a second, I get the following error:
mysql> ALTER TABLE gc_call_info ADD CONSTRAINT FK_gc_call_info_2 FOREIGN KEY FK_gc_call_info_2 (`call_type_id`) REFERENCES call
type_list (`call_type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1005 (HY000): Can't create table '.\gc_test_db\#sql-7c0_5.frm' (errno: 150)
The output from my SHOW INNODB STATUS is:
*************************** 1. row ***************************
Status:
=====================================
050413 14:59:23 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 33, signal count 33
Mutex spin waits 18, rounds 240, OS waits 6
RW-shared spins 54, OS waits 27; RW-excl spins 0, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
050413 14:16:49 Error in foreign key constraint of table gc_test_db/#sql-7c0_5:
FOREIGN KEY FK_gc_call_info_2 (`call_type_id`) REFERENCES call_type_list (`call_type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
:
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.
See
http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 866
Purge done for trx's n:o < 0 841 undo n:o < 0 0
History list length 7
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 3284
MySQL thread id 22, query id 445 localhost 127.0.0.1 root
---TRANSACTION 0 821, not started, OS thread id 3344
MySQL thread id 14, query id 484 localhost 127.0.0.1 root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (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
0 OS file reads, 528 OS file writes, 320 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 149519, used cells 1, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 128659
Log flushed up to 0 128659
Last checkpoint at 0 128659
0 pending log writes, 0 pending chkp writes
62 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 45118986; in additional pool allocated 256000
Buffer pool size 2304
Free buffers 2113
Database pages 190
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 0, created 190, written 434
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 3580, state: waiting for server activity
Number of rows inserted 14, updated 0, deleted 0, read 63
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)
ERROR:
No query specified
-Brett Berry