MYSQL是否会对外码进行检查? MYSQL是否会对外码进行检查?现在有二张表,table1 和table2,他们各自互为对方的外码,这样的话就不能往table2和table1中存入数据,因为有种死锁的味道~MYSQL会对这种情况进行检查吗?好像现在就碰到这种问题了,没对他们进行检查。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 MySQL中目前InnoDB存储引擎支持外键约束。MyISAM不支持。你的表的存储引擎是什么? 可以通过show create table table1; 查看。 如果是innodb存储引擎,则由于相互外键参照,你只能先插入table1一条,并且外键字段为null,然后再插入table2下一条之后,你可以再update table1mysql> create table table1( -> id1 int primary key, -> id2 int , -> c1 int -> ) ENGINE=InnoDB;Query OK, 0 rows affected (0.08 sec)mysql> create table table2( -> id2 int primary key, -> id1 int references table1(id1), -> c2 int, -> FOREIGN KEY (id1) REFERENCES table1(id1) -> ) ENGINE=InnoDB;Query OK, 0 rows affected (0.09 sec)mysql> alter table table1 add FOREIGN KEY (id2) REFERENCES table2(id2);Query OK, 0 rows affected (0.14 sec)Records: 0 Duplicates: 0 Warnings: 0mysql>mysql> insert into table1 values (1,1,1);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`table1`, CONSTRAINT `table1_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `table2` (`id2`))mysql>mysql> insert into table1 values (1,null,1);Query OK, 1 row affected (0.03 sec)mysql> insert into table2 values (1,1,1);Query OK, 1 row affected (0.06 sec)mysql> update table1 set id2=1 where id1=1;Query OK, 1 row affected (0.06 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from table1;+-----+------+------+| id1 | id2 | c1 |+-----+------+------+| 1 | 1 | 1 |+-----+------+------+1 row in set (0.00 sec)mysql> select * from table2;+-----+------+------+| id2 | id1 | c2 |+-----+------+------+| 1 | 1 | 1 |+-----+------+------+1 row in set (0.00 sec)mysql> UPDATE语句怎么更新blob字段??? mysql 怎么使用游标? mysql临时表无法插入,求高手 libmysql 连接 mysql 使用mysql_query内存泄露的问题 mysql的问题 急!!!!!!Mysql子查询限制条件的一个问题 表里面要同时存放简体中文,繁体中文,英文的情况下应该用什么编码比较好? 怎么把MYSQL的数据导入到ORACLE? mysql left join 效率优化问题 python服务器代码和android studio代码可以无修改跨平台运行吗? postgresq 数据库性能监视 mySql 不支持 check么?
你的表的存储引擎是什么? 可以通过show create table table1; 查看。
-> id1 int primary key,
-> id2 int ,
-> c1 int
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)mysql> create table table2(
-> id2 int primary key,
-> id1 int references table1(id1),
-> c2 int,
-> FOREIGN KEY (id1) REFERENCES table1(id1)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)mysql> alter table table1 add FOREIGN KEY (id2) REFERENCES table2(id2);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql>
mysql> insert into table1 values (1,1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`table1`, CONSTRAINT `table1_ibfk_1` FOREIGN KEY (`id2`) REFERENCES
`table2` (`id2`))
mysql>
mysql> insert into table1 values (1,null,1);
Query OK, 1 row affected (0.03 sec)mysql> insert into table2 values (1,1,1);
Query OK, 1 row affected (0.06 sec)mysql> update table1 set id2=1 where id1=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from table1;
+-----+------+------+
| id1 | id2 | c1 |
+-----+------+------+
| 1 | 1 | 1 |
+-----+------+------+
1 row in set (0.00 sec)mysql> select * from table2;
+-----+------+------+
| id2 | id1 | c2 |
+-----+------+------+
| 1 | 1 | 1 |
+-----+------+------+
1 row in set (0.00 sec)mysql>