create table a
(
aid int not null auto_increment,
aname varchar(100) not null,
primary key (aid)
);
create table b
(
bid int not null auto_increment,
bname int not null,
aid int not null,
primary key (bid));alter table b add constraint FK_Reference_5 foreign key (aid)
references a (aid) on update cascade on delete no action;我想实现的功能是 a 表中的 aid修改了则 b表中的 aid 也同时进行修改
a 表中的数据删除了 b表中的数据不能删除
我上面的代码错在那里了啊?
(
aid int not null auto_increment,
aname varchar(100) not null,
primary key (aid)
);
create table b
(
bid int not null auto_increment,
bname int not null,
aid int not null,
primary key (bid));alter table b add constraint FK_Reference_5 foreign key (aid)
references a (aid) on update cascade on delete no action;我想实现的功能是 a 表中的 aid修改了则 b表中的 aid 也同时进行修改
a 表中的数据删除了 b表中的数据不能删除
我上面的代码错在那里了啊?
sql 执行错误 #1451 从数据库的响应:
cannot delete or updata a parent row:a foreige key constraint fails ('test'.'b',constraint 'FK_Reference_5' foreige key ('aid') references 'a '('aid') on update cascade on delete no action;
)
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;#
# Source for table a
#DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`aname` varchar(100) NOT NULL,
PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;#
# Dumping data for table a
#LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES (2,'1');
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;#
# Source for table b
#DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
`bid` int(11) NOT NULL AUTO_INCREMENT,
`bname` int(11) NOT NULL,
`aid` int(11) NOT NULL,
PRIMARY KEY (`bid`),
KEY `FK_Reference_5` (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;#
# Dumping data for table b
#LOCK TABLES `b` WRITE;
/*!40000 ALTER TABLE `b` DISABLE KEYS */;
INSERT INTO `b` VALUES (1,1,2);
/*!40000 ALTER TABLE `b` ENABLE KEYS */;
UNLOCK TABLES;#
# Foreign keys for table b
#ALTER TABLE `b`
ADD CONSTRAINT `FK_Reference_5` FOREIGN KEY (`aid`) REFERENCES `a` (`aid`) ON DELETE NO ACTION ON UPDATE CASCADE;