Can't create table (errno: 150) - Foreign Key Constraints

[jarea]

I have read quite a bit about this error but I have yet to find the solution to my problem.

I am trying to execute the following mysql statement:

alter table line_items 
               add constraint fk_line_item_products 
               foreign key  (product_id) references products(id)
 

I have also tried the following statement with the same result:

alter table line_items 
               add constraint  fk_line_item_products 
               foreign key product_idx(product_id) references products(id)
 

which produces the following error message
Can't create table './depot_development/#sql_1.frm' (errno: 150)

Research to this point has clued me into the following where source field is line_items(product_id) and reference field is product(id):

1) type of foreign key source and reference fields must be identical
check: both set to int(11)

2) both source and reference fields must be unsigned
check: both fields are unsigned

3) source field must be indexed
check: product_id is indexed by product_idx

4) both tables must be InnoDB
check: both table InnoDB

Here is a dump of the two tables:

# Dump of table line_items
# ------------------------------------------------------------
 
CREATE TABLE `line_items` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `product_id` int(11) unsigned NOT NULL default '0',
  `order_id` int(11) NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0',
  `total_price` decimal(8,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`),
  KEY `product_idx` (`product_id`)
) TYPE=InnoDB;
 
 
 
# Dump of table products
# ------------------------------------------------------------
 
CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `description` text,
  `image_url` varchar(255) default NULL,
  `price` decimal(8,2) default '0.00',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB;
 

Here is the InnoDB monitor output:

RW-shared spins 95, OS waits 46; RW-excl spins 1, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
090306 10:58:32 Error in foreign key constraint of table `depot_development/#sql-126_1`:
 
               foreign key  (product_id) references products(id):
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://www.innodb.com/ibman.php for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 3697
Purge done for trx's n:o < 0 3686 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 3696, not started, OS thread id 25332736
MySQL thread id 1, query id 744 localhost root
show innodb status
--------
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
65 OS file reads, 836 OS file writes, 471 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,
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 450845
Log flushed up to   0 450845
Last checkpoint at  0 450845
0 pending log writes, 0 pending chkp writes
324 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 18682098; in additional pool allocated 912128
Buffer pool size   512
Free buffers       476
Database pages     35
Modified db pages  0
Pending reads 0 
Pending writes: LRU 0, flush list 0, single page 0
Pages read 26, created 9, written 792
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 25284096, state: waiting for server activity
Number of rows inserted 49, updated 4, deleted 0, read 125
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 

Any help would be greatly appreciated.

[jarea]

It turns out that while the table was not created, the foreign key was. When I dropped the foreign key fk_line_key_products and reran the process, all worked fine.

Whew!

[Gary Feltham]

Just a note for those that stumble across this. Check the collation of the 2 tables as these must also match.