锐客网

  1. 首页 > 睿知 > it技术 > >

mysql级联删除怎么写 mysql 级联删除

IT技术删除

mysql 两个外键级联删除的问题DROP TABLE IF EXISTS `courseteacher`;
CREATE TABLE`courseteacher` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tno` int(10) unsigned NOT NULL,
`cno` int(10) unsigned NOT NULL,
PRIMARY KEY (`ID`),
KEY `FK_courseteacher_1teacher1` (`tno`),
KEY `FK_courseteacher_2classes1` (`cno`),
CONSTRAINT `FK_courseteacher_1terchar1` FOREIGN KEY (`tno`) REFERENCES `Teacher` (`tno`) ON DELETE CASCADE,
CONSTRAINT `FK_courseteacher_2clases1` FOREIGN KEY (`cno`) REFERENCES `Course` (`cno`) ON DELETE CASCADE
) ENGINE=InnoDBDEFAULT CHARSET=utf8;
试过没问题啊mysql级联删除怎么写，mysql级联删除怎么写你往这个表插入数据的前提是tno和cno必须存在于Teacher表和Course表
怎么写mysql数据库延迟级联删除命令举例：
1、建筑表
CREATE
TABLE
buildings
(
building_no
INT
PRIMARY
KEY
AUTO_INCREMENT,
building_name
VARCHAR(255)
NOT
NULL,
address
VARCHAR(255)
NOT
NULL
);
2、房间表
CREATE
TABLE
rooms
(
room_no
INT
PRIMARY
KEY
AUTO_INCREMENT,
room_name
VARCHAR(255)
NOT
NULL,
building_no
INT
NOT
NULL,
FOREIGN
KEY
(building_no)
REFERENCES
buildings
(building_no)
ON
DELETE
CASCADE
//这里指定了级联删除
);
3、执行删除会把building_no=2的room记录都删除了
DELETE
FROM
buildings
WHERE
building_no
=
2;
mysql怎么设置级联删除这个在建表的时候就可以指定了
CREATE TABLE buildings (
building_no INT PRIMARY KEY AUTO_INCREMENT,
building_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
);
CREATE TABLE rooms (
room_no INT PRIMARY KEY AUTO_INCREMENT,
room_name VARCHAR(255) NOT NULL,
building_no INT NOT NULL,
FOREIGN KEY (building_no)
REFERENCES buildings (building_no)
ON DELETE CASCADE
);
当执行
DELETE FROM buildings
WHERE
building_no = 2;的时候，rooms表中的building_no =2的记录都会被删掉 。
mysql级联删除触发器你的数据库访问层是不是用Hibernate的？如果是Hibernate的话，可以再hbm.xml文件里设置cascade=“delete”就可以进行级联操作了 。
如果不是Hibernate，而是用JdBC的话 ， 且使用触发器的话，就要按照MySQL的触发器的编写规范来写了 ， 你的这个触发器像是Oracle的 。
MySQL写触发器 ， 级联删除我就不造你的 学生表和成绩表了.
用 test_main 表 和 test_sub代替说明
-- 创建测试主表. ID 是主键.
CREATE TABLE test_main (
idINTNOT NULL,
valueVARCHAR(10),
PRIMARY KEY(id)
);
-- 创建测试子表.
CREATE TABLE test_sub (
idINTNOT NULL,
main_id INT ,
valueVARCHAR(10),
PRIMARY KEY(id)
);
-- 插入测试主表数据.
INSERT INTO test_main(id, value) VALUES (1, 'ONE');
INSERT INTO test_main(id, value) VALUES (2, 'TWO');
-- 插入测试子表数据.
INSERT INTO test_sub(id, main_id, value) VALUES (1, 1, 'ONEONE');
INSERT INTO test_sub(id, main_id, value) VALUES (2, 2, 'TWOTWO');
通过DELETE CASCADE外键约束的处理机制来处理
mysql ALTER TABLE test_sub
-ADD CONSTRAINT main_id_cons
-FOREIGN KEY (main_id)
-REFERENCEStest_main(id)
-ON DELETE CASCADE//
Query OK, 2 rows affected (0.16 sec)
Records: 2Duplicates: 0Warnings: 0
mysql DELETE FROM
-test_main
- WHERE
-id = 1;
- //
Query OK, 1 row affected (0.02 sec)
mysql SELECT
-*

推荐阅读


上一篇：for命令linux，for命令是什么意思

下一篇：手游平台充值代理费，游戏充值代理