//创建表parent,并插入数据
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into parent values(1),(2),(3);
// 创建表child,并插入数据
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into child values(10,1),(20,2),(30,3);
// 为child表添加一个外键,与parent表的关系是删除联动
// 当parent表的记录被删除时,如果指定的外键parent_id=id时,child表的相对应的记录也同时被删除
ALTER TABLE child ADD CONSTRAINT child_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;
//删除parent表id=2的记录
delete from parent where id=2;
// 发现child表parent_id=2的记录也被删除掉了
select * from child;
CREATE TABLE `text_persion` (
`id` varchar(36) NOT NULL COMMENT 'id',
`name` varchar(100) NOT NULL COMMENT '姓名',
`sex` varchar(100) NOT NULL COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='信息表';
CREATE TABLE `test_ref_persion` (
`id` varchar(36) NOT NULL COMMENT 'ID',
`ref_id` varchar(100) NOT NULL COMMENT '外键ID',
`occupa` varchar(6) DEFAULT NULL COMMENT '职业',
PRIMARY KEY (`id`),
KEY `FK_Reference` (`ref_id`),
CONSTRAINT `FK_Reference` FOREIGN KEY (`ref_id`) REFERENCES `text_persion` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='text_persion的附表';
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into parent values(1),(2),(3);
// 创建表child,并插入数据
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into child values(10,1),(20,2),(30,3);
// 为child表添加一个外键,与parent表的关系是删除联动
// 当parent表的记录被删除时,如果指定的外键parent_id=id时,child表的相对应的记录也同时被删除
ALTER TABLE child ADD CONSTRAINT child_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;
//删除parent表id=2的记录
delete from parent where id=2;
// 发现child表parent_id=2的记录也被删除掉了
select * from child;
CREATE TABLE `text_persion` (
`id` varchar(36) NOT NULL COMMENT 'id',
`name` varchar(100) NOT NULL COMMENT '姓名',
`sex` varchar(100) NOT NULL COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='信息表';
CREATE TABLE `test_ref_persion` (
`id` varchar(36) NOT NULL COMMENT 'ID',
`ref_id` varchar(100) NOT NULL COMMENT '外键ID',
`occupa` varchar(6) DEFAULT NULL COMMENT '职业',
PRIMARY KEY (`id`),
KEY `FK_Reference` (`ref_id`),
CONSTRAINT `FK_Reference` FOREIGN KEY (`ref_id`) REFERENCES `text_persion` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='text_persion的附表';