By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,965 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,965 IT Pros & Developers. It's quick & easy.

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

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. alter table line_items 
  2.                add constraint fk_line_item_products 
  3.                foreign key  (product_id) references products(id)
  4.  
I have also tried the following statement with the same result:
Expand|Select|Wrap|Line Numbers
  1. alter table line_items 
  2.                add constraint  fk_line_item_products 
  3.                foreign key product_idx(product_id) references products(id)
  4.  
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:

Expand|Select|Wrap|Line Numbers
  1. # Dump of table line_items
  2. # ------------------------------------------------------------
  3.  
  4. CREATE TABLE `line_items` (
  5.   `id` int(11) unsigned NOT NULL auto_increment,
  6.   `product_id` int(11) unsigned NOT NULL default '0',
  7.   `order_id` int(11) NOT NULL default '0',
  8.   `quantity` int(11) NOT NULL default '0',
  9.   `total_price` decimal(8,2) NOT NULL default '0.00',
  10.   PRIMARY KEY  (`id`),
  11.   KEY `product_idx` (`product_id`)
  12. ) TYPE=InnoDB;
  13.  
  14.  
  15.  
  16. # Dump of table products
  17. # ------------------------------------------------------------
  18.  
  19. CREATE TABLE `products` (
  20.   `id` int(11) unsigned NOT NULL auto_increment,
  21.   `title` varchar(255) default NULL,
  22.   `description` text,
  23.   `image_url` varchar(255) default NULL,
  24.   `price` decimal(8,2) default '0.00',
  25.   PRIMARY KEY  (`id`)
  26. ) TYPE=InnoDB;
  27.  
Here is the InnoDB monitor output:

Expand|Select|Wrap|Line Numbers
  1. RW-shared spins 95, OS waits 46; RW-excl spins 1, OS waits 0
  2. ------------------------
  3. LATEST FOREIGN KEY ERROR
  4. ------------------------
  5. 090306 10:58:32 Error in foreign key constraint of table `depot_development/#sql-126_1`:
  6.  
  7.                foreign key  (product_id) references products(id):
  8. Cannot find an index in the referenced table where the
  9. referenced columns appear as the first columns, or column types
  10. in the table and the referenced table do not match for constraint.
  11. See http://www.innodb.com/ibman.php for correct foreign key definition.
  12. ------------
  13. TRANSACTIONS
  14. ------------
  15. Trx id counter 0 3697
  16. Purge done for trx's n:o < 0 3686 undo n:o < 0 0
  17. Total number of lock structs in row lock hash table 0
  18. LIST OF TRANSACTIONS FOR EACH SESSION:
  19. ---TRANSACTION 0 3696, not started, OS thread id 25332736
  20. MySQL thread id 1, query id 744 localhost root
  21. show innodb status
  22. --------
  23. FILE I/O
  24. --------
  25. I/O thread 0 state: waiting for i/o request (insert buffer thread)
  26. I/O thread 1 state: waiting for i/o request (log thread)
  27. I/O thread 2 state: waiting for i/o request (read thread)
  28. I/O thread 3 state: waiting for i/o request (write thread)
  29. Pending normal aio reads: 0, aio writes: 0,
  30.  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
  31. Pending flushes (fsync) log: 0; buffer pool: 0
  32. 65 OS file reads, 836 OS file writes, 471 OS fsyncs
  33. 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
  34. -------------------------------------
  35. INSERT BUFFER AND ADAPTIVE HASH INDEX
  36. -------------------------------------
  37. Ibuf for space 0: size 1, free list len 0, seg size 2,
  38. 0 inserts, 0 merged recs, 0 merges
  39. Hash table size 34679, used cells 0, node heap has 1 buffer(s)
  40. 0.00 hash searches/s, 0.00 non-hash searches/s
  41. ---
  42. LOG
  43. ---
  44. Log sequence number 0 450845
  45. Log flushed up to   0 450845
  46. Last checkpoint at  0 450845
  47. 0 pending log writes, 0 pending chkp writes
  48. 324 log i/o's done, 0.00 log i/o's/second
  49. ----------------------
  50. BUFFER POOL AND MEMORY
  51. ----------------------
  52. Total memory allocated 18682098; in additional pool allocated 912128
  53. Buffer pool size   512
  54. Free buffers       476
  55. Database pages     35
  56. Modified db pages  0
  57. Pending reads 0 
  58. Pending writes: LRU 0, flush list 0, single page 0
  59. Pages read 26, created 9, written 792
  60. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  61. No buffer pool page gets since the last printout
  62. --------------
  63. ROW OPERATIONS
  64. --------------
  65. 0 queries inside InnoDB, 0 queries in queue
  66. Main thread id 25284096, state: waiting for server activity
  67. Number of rows inserted 49, updated 4, deleted 0, read 125
  68. 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
  69. ----------------------------
  70. END OF INNODB MONITOR OUTPUT
  71. ============================
  72.  
Any help would be greatly appreciated.
Mar 6 '09 #1
Share this Question
Share on Google+
2 Replies


P: 2
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!
Mar 6 '09 #2

P: n/a
Just a note for those that stumble across this. Check the collation of the 2 tables as these must also match.
Nov 10 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.