Description:
on 5.0.19
mysql> use test;
Database changed
mysql> create table departments(
-> id int not null,
-> name varchar(50) not null,
-> primary key (id))engine=innodb;
Query OK, 0 rows affected (0.01 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.03 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;
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| Table | Create Table
|
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| employees | 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)
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)
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)
but on 5.1.17-beta it says yes,none records affect
mysql> alter table employees drop foreign key employees_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
How to repeat:
This is my first to report a bug.My english is poor.So maybe I don't say it very clearly,but I hope you can understand it.Maybe someone have report it,but I can't see it.
My e-mail address is:gnayfly@163.com
Description: on 5.0.19 mysql> use test; Database changed mysql> create table departments( -> id int not null, -> name varchar(50) not null, -> primary key (id))engine=innodb; Query OK, 0 rows affected (0.01 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.03 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; +-----------+------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------+ | Table | Create Table | +-----------+------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------+ | employees | 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) 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) 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) but on 5.1.17-beta it says yes,none records affect mysql> alter table employees drop foreign key employees_ibfk_1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 How to repeat: This is my first to report a bug.My english is poor.So maybe I don't say it very clearly,but I hope you can understand it.Maybe someone have report it,but I can't see it. My e-mail address is:gnayfly@163.com