mysql> show create table persons;
+---------+---------------------------------------------------------------------------------------------------
| Table | Create Table
+---------+---------------------------------------------------------------------------------------------------
| persons | CREATE TABLE `persons` (
`id` int(11) NOT NULL,
`FistName` varchar(20) DEFAULT NULL,
`Address` varchar(20) DEFAULT NULL,
`City` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `address_1` (`Address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql> show create table orders2;
+---------+---------------------------------------------------------------------------------------------------
| Table | Create Table
+---------+---------------------------------------------------------------------------------------------------
| orders2 | CREATE TABLE `orders2` (
`ID_O` int(11) NOT NULL,
`OrderNo` int(8) DEFAULT NULL,
`ID_P` int(11) DEFAULT NULL,
KEY `id_p1` (`ID_P`),
CONSTRAINT `id_p1` FOREIGN KEY (`ID_P`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)定义了上面两个表,在同一个数据库。但是我现在在persons表中进行删除或者修改操作都报出以下的错误提示。
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`samp_db`.`orders1`, CONSTRAINT `orders1_ibfk_1` FOREIGN KEY (`ID_P`) REFERENCES `persons` (`id`))所以是哪里出错了吗?初学mysql,请大家指教下!多谢!!!
+---------+---------------------------------------------------------------------------------------------------
| Table | Create Table
+---------+---------------------------------------------------------------------------------------------------
| persons | CREATE TABLE `persons` (
`id` int(11) NOT NULL,
`FistName` varchar(20) DEFAULT NULL,
`Address` varchar(20) DEFAULT NULL,
`City` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `address_1` (`Address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql> show create table orders2;
+---------+---------------------------------------------------------------------------------------------------
| Table | Create Table
+---------+---------------------------------------------------------------------------------------------------
| orders2 | CREATE TABLE `orders2` (
`ID_O` int(11) NOT NULL,
`OrderNo` int(8) DEFAULT NULL,
`ID_P` int(11) DEFAULT NULL,
KEY `id_p1` (`ID_P`),
CONSTRAINT `id_p1` FOREIGN KEY (`ID_P`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)定义了上面两个表,在同一个数据库。但是我现在在persons表中进行删除或者修改操作都报出以下的错误提示。
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`samp_db`.`orders1`, CONSTRAINT `orders1_ibfk_1` FOREIGN KEY (`ID_P`) REFERENCES `persons` (`id`))所以是哪里出错了吗?初学mysql,请大家指教下!多谢!!!
+----+----------+----------------+----------+
| id | FistName | Address | City |
+----+----------+----------------+----------+
| 1 | John | Oxford Street | London |
| 2 | George | Fifth Avenue | New York |
| 3 | Thomas | Changan Street | Beijing |
| 5 | Bill | Xuanwumen 10 | Beijing |
+----+----------+----------------+----------+
4 rows in set (0.00 sec)mysql> select * from orders2;
+------+---------+------+
| ID_O | OrderNo | ID_P |
+------+---------+------+
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
+------+---------+------+
2 rows in set (0.03 sec)mysql> update persons
-> set id = 4
-> where City = 'London';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`samp_db`.`orders1`, CONSTRAINT `orders1_ibfk_1` FOREIGN KEY (`ID_P`) REFERENCES `persons` (`id`))
mysql>可是外键不是设定为 on delete cascade on update cascade了吗?按理是可以对父表进行修改操作的,可是总是报出错误。
+----+----------+----------------+----------+
| id | FistName | Address | City |
+----+----------+----------------+----------+
| 1 | John | Oxford Street | London |
| 2 | George | Fifth Avenue | New York |
| 3 | Thomas | Changan Street | Beijing |
| 5 | Bill | Xuanwumen 10 | Beijing |
+----+----------+----------------+----------+
4 rows in set (0.00 sec)mysql> select * from orders2;
+------+---------+------+
| ID_O | OrderNo | ID_P |
+------+---------+------+
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
+------+---------+------+
2 rows in set (0.03 sec)mysql> update persons
-> set id = 4
-> where City = 'London';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`samp_db`.`orders1`, CONSTRAINT `orders1_ibfk_1` FOREIGN KEY (`ID_P`) REFERENCES `persons` (`id`))
mysql>设置外键时应用了on delete cascade on update cascade, 但是对父表进行修改,却总是报错。是哪里出错了吗?
+---------+---------------------------------------------------------------------------------------------------
| Table | Create Table
+---------+---------------------------------------------------------------------------------------------------
| orders2 | CREATE TABLE `orders2` (
`ID_O` int(11) NOT NULL,
`OrderNo` int(8) DEFAULT NULL,
`ID_P` int(11) DEFAULT NULL,
KEY `id_p1` (`ID_P`),
CONSTRAINT `id_p1` FOREIGN KEY (`ID_P`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------
1 row in set (0.31 sec)