mysql alter table add foreign key_mysql alter table add foreign (errno: 150)添加外键150错误

在原有的users 表和orders表上，为orders添加外键alter table tbl_order

add foreign key fk_user_id(user_id)

references mgie_users(ID)

on update cascade

on delete restrict;

错误

10:56:45    alter table tbl_order add foreign key fk_user_id(user_id) references mgie_users(ID) on update cascade on delete restrict    Error Code: 1005. Can't create table 'prudential_dev.#sql-c79_70ea7' (errno: 150)    0.343 secSHOW ENGINE INNODB STATUS

=====================================

151130 10:56:40 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 38 seconds

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 79951, signal count 79266

Mutex spin waits 0, rounds 1233440, OS waits 60920

RW-shared spins 24852, OS waits 11122; RW-excl spins 8210, OS waits 7908

------------------------

LATEST FOREIGN KEY ERROR

------------------------

151130 10:56:37 Error in foreign key constraint of table prudential_dev/#sql-c79_70ea7:

foreign key fk_user_id(user_id)

references mgie_users(ID)

on update cascade

on delete 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.

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.1/en/innodb-foreign-key-constraints.html

for correct foreign key definition.

------------

TRANSACTIONS

------------

Trx id counter 0 4472213

Purge done for trx's n:o < 0 4472154 undo n:o < 0 0

History list length 0

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 4472128, not started, process no 3193, OS thread id 140089513969408

MySQL thread id 462503, query id 8867409 localhost 127.0.0.1 root

SHOW ENGINE INNODB STATUS

---TRANSACTION 0 4472147, not started, process no 3193, OS thread id 140089509177088

MySQL thread id 462502, query id 8867407 localhost 127.0.0.1 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

389585 OS file reads, 488497 OS file writes, 359299 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.03 writes/s, 0.03 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 5, seg size 7,

6071 inserts, 6071 merged recs, 1332 merges

Hash table size 17393, node heap has 9 buffer(s)

0.26 hash searches/s, 3.39 non-hash searches/s

---

LOG

---

Log sequence number 0 3546060929

Log flushed up to   0 3546060929

Last checkpoint at  0 3546041328

0 pending log writes, 0 pending chkp writes

330831 log i/o's done, 0.03 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 29859762; in additional pool allocated 1048320

Dictionary memory allocated 7909360

Buffer pool size   512

Free buffers       1

Database pages     502

Modified db pages  28

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages read 486345, created 29879, written 240385

0.00 reads/s, 0.00 creates/s, 0.00 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 process no. 3193, id 140089526322944, state: sleeping

Number of rows inserted 438705, updated 111994, deleted 270073, read 23002138

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

show create table tbl_order;

CREATE TABLE `tbl_order` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`user_id` bigint(20) unsigned NOT NULL,

`order_key` varchar(50) NOT NULL DEFAULT '',

`email` varchar(100) NOT NULL DEFAULT '',

`first_name` varchar(50) NOT NULL DEFAULT '',

`last_name` varchar(50) NOT NULL DEFAULT '',

`contact_number` varchar(20) NOT NULL DEFAULT '',

`location_code` varchar(20) NOT NULL DEFAULT '',

`division_code` varchar(20) NOT NULL DEFAULT '',

`agent_code` varchar(20) NOT NULL DEFAULT '',

`department` varchar(20) NOT NULL DEFAULT '',

`payment_method` varchar(20) NOT NULL DEFAULT '',

`order_total` decimal(20,2) NOT NULL,

`delivery_address` varchar(100) NOT NULL DEFAULT '',

`status` varchar(20) NOT NULL DEFAULT '',

`creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`last_modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`last_modified_by` varchar(20) NOT NULL DEFAULT '',

`customer_ip_address` varchar(50) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`),

KEY `email` (`email`),

KEY `first_name` (`first_name`),

KEY `last_name` (`last_name`),

KEY `location_code` (`location_code`),

KEY `division_code` (`division_code`),

KEY `agent_code` (`agent_code`),

KEY `creation_date` (`creation_date`),

KEY `status` (`status`),

CONSTRAINT `tbl_order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`ID`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8