我想在mysql中将t_flow_card_detail表中的client_id字段改为外键,引用t_client表中的client_id字段:
我的sql语句是: alter table t_flow_card_detail add constraint FK_flowcard_client foreign key (client_id) references t_client(client_id);
可总是报错:ERROR 1005 (HY000): Can't create table '.\drp\#sql-750_3.frm' (errno: 150)请哪位能告诉我怎么修改。。谢谢啦。。
我的sql语句是: alter table t_flow_card_detail add constraint FK_flowcard_client foreign key (client_id) references t_client(client_id);
可总是报错:ERROR 1005 (HY000): Can't create table '.\drp\#sql-750_3.frm' (errno: 150)请哪位能告诉我怎么修改。。谢谢啦。。
看一下表的存储引擎,innodb存储引擎才支持外键约束。
show create table 表名;检查2个表的client_id数据类型是否相同:
show create table 表名;检查t_client表中的client_id字段是否为key
根据你的提示,发现client_id 不是key 。再问一下,我的一个表的编码为utf8,另一个为gb2312。有影响嘛?是否能更改?
+-------+--------------------------------------------------
-----------------------------------------------------------
---------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------
-----------------------------------------------------------
---------------------------------------+
| t | CREATE TABLE `t` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(15) DEFAULT NULL,
`value` int(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------
-----------------------------------------------------------
---------------------------------------+
1 row in set (0.02 sec)mysql> alter table t default charset utf8;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t;
+-------+--------------------------------------------------
-----------------------------------------------------------
----------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------
-----------------------------------------------------------
----------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
`value` int(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------
-----------------------------------------------------------
----------------------------------------------------------+
1 row in set (0.00 sec)
有影响
将数据导出,然后 alter 或 create table,再将数据导入。
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html