mysql error 1215: Cannot add foreign key constraint

[thomasLand]

mysql> CREATE TABLE IF NOT EXISTS `item_general`(
-> id VARCHAR(32)
-> );
ERROR 1215 (HY000): Cannot add foreign key constraint

[thomasLand]

很难碰到的环境...求指导。

[thomasLand]

再次感谢wwwwb版主大人的指导。至于mysql丢失连接的问题不再深究。 论坛这么多讲物理数据库优化的，有一点想法: B+树是DB2、Oracle、SQL Server支持的基本索引方法，几乎占据了所有系统场景。 (摘自《物理数据库设计 -- 索引、视图和存储技术》） 能不能基于B+树将典型的业务场景(Mysql为例)汇总下。以便讨论和查阅。这个重复率太高了。

[wwwwb]

检查一下是否有约束指向了此表（原来有约束后删除了此表？）

[thomasLand]

非常感谢wwwwb的指导,原因如下: mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE where table_schema='be' and constraint_name like '%item_general%'; +--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def | be | FK_RINVOICE_DETAILS_ITEM_GENERAL | def | be | rinvoice_details | item_id | 1 | 1 | be | item_general | item_id | +--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE `a` -> ( -> `B_ID` VARCHAR(32) -> ); Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE `a` ADD CONSTRAINT FK_A_B FOREIGN key (`b_id`) REFERENCES `b`(`id`); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE `b` -> ( -> `ID` VARCHAR(32) -> ); ERROR 1215 (HY000): Cannot add foreign key constraint 话说,早上来到之后登录mysql之后,运行个名为ｘｘｘｙｙｙ的脚本。提示丢失连接。得知原因是服务神奇般的死掉了。 好吧,开启就是。service.msc（当前的系统为Windows7+Mysql 5.6）。被域控制! 之后傻乎乎的Win+R cd MYSQL_HOME/bin mysqld -install 出错,安装被拒绝。 无奈,找了个管理员权限。 C:/windows/System32/cms.exe--> 以管理员权限运行。 cd MYSQL_HOME/bin mysqld -install net start mysql 之后悲剧出现了!!!! mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) 我的数据库实例不见了....好吧,日子还是要过的。风水不是太好，重新开个ＣＭＤ窗口。 ｍｙｓｑｌ　－ｕ　ｒｏｏｔ　－ｐ ＊＊＊＊＊＊ 我被ｌｏｃａｌｈｏｓｔ拒绝了。。。密码重新输入一遍，一样的结果。 光脚的不怕穿鞋的。 ｍｙｓｑｌ　－ｕ　ｒｏｏｔ 顺利进去了．．．． 再看，ｍｙ．ｉｎｉ # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 以上，求扫盲，求科普。

[wwwwb]

basedir datadir 设置为你的工作目录试试

[thomasLand]

设置之后,没有什么改变。不过无所谓了,这是开发用的PC，而且还有数据备份。 只是有些好奇: 刚刚使用 mysql -u root -p xxxxxx 登录进去,mysql怎么就丢失连接了呢?（什么原因会造成mysql连接丢失） 以管理员身份安装服务后,为何丢失数据?(安装mysql服务意味着什么？应该不单单是开启一个端口号那么简单吧。)

[thomasLand]