mysql> create table departments(
-> id int not null,
-> name varchar(50) not null,
-> primary key (id))engine=innodb;
Query OK, 0 rows affected (0.03 sec)mysql> create table employees(
-> id smallint(6) not null,
-> name char(255) not null,
-> fk_department int not null,
-> primary key(id),
-> index (fk_department),
-> foreign key (fk_department) references departments (id))engine=innodb;
Query OK, 0 rows affected (0.03 sec)mysql> insert into departments (id,name) values(101,'Engineering');
Query OK, 1 row affected (0.01 sec)mysql> insert into departments (id,name) values(102,'Administration');
Query OK, 1 row affected (0.00 sec)mysql> insert into departments (id,name) values(103,'Finance');
Query OK, 1 row affected (0.00 sec)mysql> insert into employees(id,name,fk_department) values (57,'John D',102);
Query OK, 1 row affected (0.00 sec)mysql> insert into employees(id,name,fk_department) values (101,'Thomas E',110)
;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test/employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`fk_departmen
t`) REFERENCES `departments` (`id`))mysql> show create table employees \G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` smallint(6) NOT NULL,
`name` char(255) NOT NULL,
`fk_department` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_department` (`fk_department`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`fk_department`) REFERENCES `depart
ments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)从上面找到foreign key 的值 employees_ibfk_1,然后再用下面的语句删除,手册上是这么说的,不过不知道为什么我的不成功。5.0.19会报错:
mysql> alter table employees drop foreign key employees_idfk_1;
ERROR 1025 (HY000): Error on rename of '.\test\employees' to '.\test\#sql2-434-1
' (errno: 152)而5.1.17-beta则没有反应:
mysql> alter table employees drop foreign key employees_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-> id int not null,
-> name varchar(50) not null,
-> primary key (id))engine=innodb;
Query OK, 0 rows affected (0.03 sec)mysql> create table employees(
-> id smallint(6) not null,
-> name char(255) not null,
-> fk_department int not null,
-> primary key(id),
-> index (fk_department),
-> foreign key (fk_department) references departments (id))engine=innodb;
Query OK, 0 rows affected (0.03 sec)mysql> insert into departments (id,name) values(101,'Engineering');
Query OK, 1 row affected (0.01 sec)mysql> insert into departments (id,name) values(102,'Administration');
Query OK, 1 row affected (0.00 sec)mysql> insert into departments (id,name) values(103,'Finance');
Query OK, 1 row affected (0.00 sec)mysql> insert into employees(id,name,fk_department) values (57,'John D',102);
Query OK, 1 row affected (0.00 sec)mysql> insert into employees(id,name,fk_department) values (101,'Thomas E',110)
;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test/employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`fk_departmen
t`) REFERENCES `departments` (`id`))mysql> show create table employees \G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` smallint(6) NOT NULL,
`name` char(255) NOT NULL,
`fk_department` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_department` (`fk_department`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`fk_department`) REFERENCES `depart
ments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)从上面找到foreign key 的值 employees_ibfk_1,然后再用下面的语句删除,手册上是这么说的,不过不知道为什么我的不成功。5.0.19会报错:
mysql> alter table employees drop foreign key employees_idfk_1;
ERROR 1025 (HY000): Error on rename of '.\test\employees' to '.\test\#sql2-434-1
' (errno: 152)而5.1.17-beta则没有反应:
mysql> alter table employees drop foreign key employees_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货