MySql不能够在一个表里的两个数据域设置同一个外键吗?

[PunWinger]

drop table if exists exprefer;

create table exprefer (
main_id		int not null,
refer_id	int not null,
primary key(main_id, refer_id),
foreign key(main_id) references labexps(id) on delete cascade on update
cascade,
foreign key(refer_id) references labexps(id) on delete cascade on update
cascade
)type=innodb;

弹出的错误代码是Can't create table (errno: 150)

[PunWinger]

create table labexps (
id            int not null primary key AUTO_INCREMENT,
...
...
)type=innodb;

这是我的labexps表,还有,我把一个外键的声明foreign key(refer_id) references labexps(id) on delete cascade on update cascade删除掉后可以建表。
2楼的那个为什么不用加type=innodb呢？我试过不加的确可以创建表，但是外键功能是没效的

[rucypli]

mysql> create table labexps(id int primary key);
Query OK, 0 rows affected (0.25 sec)

mysql> create table exprefer (
-> main_id int not null,
-> refer_id int not null,
-> primary key(main_id, refer_id),
-> foreign key(main_id) references labexps(id) on delete cascade on update
-> cascade,
-> foreign key(refer_id) references labexps(id) on delete cascade on update
-> cascade
-> )engine=innodb;
Query OK, 0 rows affected (0.28 sec)

[PunWinger]

[Quote=引用 3 楼 的回复:]

mysql> create table labexps(id int primary key);
Query OK, 0 rows affected (0.25 sec)

mysql> create table exprefer (
-> main_id int not null,
-> refer_id int not null,
->……
[/Quote]

估计是我用的版本太老了不行了,用的是EMS MySQL Manager,我删除一个外表声明之后,自己手动添加外键可以了..唉,这系统真麻烦,用的版本都太老了,想换又太麻烦

[ACMAIN_CHM]

你的另外一个表中 labexps(id) 是什么数据类型？是不是INT？ 另外有没有加唯一索引或者主键？

mysql> create table labexps(id int primary key);
Query OK, 0 rows affected (0.12 sec)

mysql> create table exprefer (
    -> main_id        int not null,
    -> refer_id    int not null,
    -> primary key(main_id, refer_id),
    -> foreign key(main_id) references labexps(id) on delete cascade on update
    -> cascade,
    -> foreign key(refer_id) references labexps(id) on delete cascade on update
    -> cascade
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql>