error: #1005 - Can't create table '.\_ftl\#sql-918_f.frm' (errno: 150)

[daveftl]

Good day to all...i always have this error after making an ALTER command ... #1005 - Can't create table '.\_ftl\#sql-918_f.frm' (errno: 150) whats the cause of this error...thanks in advance..

[amitpatel66]

Good day to all...i always have this error after making an ALTER command ... #1005 - Can't create table '.\_ftl\#sql-918_f.frm' (errno: 150) whats the cause of this error...thanks in advance..

Could you please POST the output after executing the below command:

mysql> show innodb status;

[daveftl]

mysql> show innodb status;

| Status


=====================================
071025 19:07:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 54 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7, signal count 7
Mutex spin waits 0, rounds 40, OS waits 2
RW-shared spins 8, OS waits 4; RW-excl spins 1, OS waits 1
------------------------
LATEST FOREIGN KEY ERROR
------------------------
071025 18:17:38 Error in foreign key constraint of table _ftl/#sql-918_f:

FOREIGN KEY (Order_Id, cust_ID) REFERENCES trans_Order (Order_Id,cust_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.
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 1328
Purge done for trx's n:o < 0 1299 undo n:o < 0 0
History list length 4
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 2868
MySQL thread id 19, query id 345 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
49 OS file reads, 58 OS file writes, 27 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: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 62383, 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 98760
Log flushed up to 0 98760
Last checkpoint at 0 98760
0 pending log writes, 0 pending chkp writes
25 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 23277710; in additional pool allocated 1412480
Buffer pool size 960
Free buffers 917
Database pages 42
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 39, created 3, written 35
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
1 read views open inside InnoDB
Main thread id 3208, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
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)

mysql> clear;
mysql>

[daveftl]

this is my sql code:

ALTER TABLE Bottles ADD CONSTRAINT trans_Order_Bottles
FOREIGN KEY (Order_Id, cust_ID) REFERENCES trans_Order (Order_Id,cust_ID);

ALTER TABLE trans_Order ADD CONSTRAINT Customer_trans_Order
FOREIGN KEY (cust_ID) REFERENCES Customer (cust_ID);

ALTER TABLE trans_Order ADD CONSTRAINT trans_Payment_trans_Order
FOREIGN KEY (Payment_Id) REFERENCES trans_Payment (Payment_Id);

ALTER TABLE trans_Payment ADD CONSTRAINT Customer_trans_Payment
FOREIGN KEY (cust_ID) REFERENCES Customer (cust_ID);

is there wrong with the code? thanks for the help...

[amitpatel66]

this is my sql code:

ALTER TABLE Bottles ADD CONSTRAINT trans_Order_Bottles
FOREIGN KEY (Order_Id, cust_ID) REFERENCES trans_Order (Order_Id,cust_ID);

ALTER TABLE trans_Order ADD CONSTRAINT Customer_trans_Order
FOREIGN KEY (cust_ID) REFERENCES Customer (cust_ID);

ALTER TABLE trans_Order ADD CONSTRAINT trans_Payment_trans_Order
FOREIGN KEY (Payment_Id) REFERENCES trans_Payment (Payment_Id);

ALTER TABLE trans_Payment ADD CONSTRAINT Customer_trans_Payment
FOREIGN KEY (cust_ID) REFERENCES Customer (cust_ID);

is there wrong with the code? thanks for the help...

Can you please check if the table trans_order has the primary key (order_id,cust_id) created? If not create the composite key first in trans_order and then create foreign key

[daveftl]

Can you please check if the table trans_order has the primary key (order_id,cust_id) created? If not create the composite key first in trans_order and then create foreign key

that was the reason of the error...the composite key was not created first...thanks a lot! God bless

[amitpatel66]

that was the reason of the error...the composite key was not created first...thanks a lot! God bless

Thank you for those Blessings :-)
DO POST back any issues you face in future!!
We will help you out!!