MySQL 数据库表的 ondelete 级联删除

作者: Manchangdx | 来源:发表于2018-05-25 14:05 被阅读0次

MySQL 数据库表的 ondelete 级联删除
Mysql学习——数据库基础操作（1）
mysql学习笔记
MySQL
MySQL 锁表
SQL从入门到不放弃(ing)
从0到1学习网络安全【MySQL基础-MySQL 创建数据库】
kettle学习笔记（五）——kettle输出步骤
JAVA WEB开发笔记
数据库笔记

首先，创建三个数据库表映射类 User 、Course、Lab
外键关联不用说，两个一对多的关系，ForeignKey 都是一样的，
注意 Course 类和 Lab 类中 relationship 的区别：

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker, backref
engine = create_engine('mysql://root@localhost/shiyanlou?charset=utf8')
Base = declarative_base(engine)
session = sessionmaker(engine)()
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    email = Column(String(64))
    def __repr__(self):
        return '<User: {}>'.format(self.name)
class Course(Base):
    __tablename__ = 'course'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    user_id = Column(Integer, ForeignKey('user.id', ondelete='CASCADE'))
    user = relationship('User', 
           backref=backref('course', cascade='all, delete-orphan'))
    def __repr__(self):
        return '<Course: {}>'.format(self.name)
class Lab(Base):
    __tablename__ = 'lab'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    course_id = Column(Integer, 
                ForeignKey('course.id', ondelete='CASCADE'))
    course = relationship('Course', backref='lab')
    def __repr__(self):
        return '<Lab: {}>'.format(self.name)
Base.metadata.create_all()

创建几个实例并传入数据库：

u = User(name='Kobe', email='kobe@qq.com')
c = Course(name='Flask 基础', user=u)
l = Lab(name='简单的 CRUD 操作', course=c)
session.add(u)
session.add(c)
session.add(l)
session.commit()

此时数据库的状态：

mysql> use shiyanlou
Database changed
mysql> select * from user;
+----+------+-------------+
| id | name | email       |
+----+------+-------------+
|  1 | Kobe | kobe@qq.com |
+----+------+-------------+
1 row in set (0.00 sec)

mysql> select * from course;
+----+--------------+---------+
| id | name         | user_id |
+----+--------------+---------+
|  1 | Flask 基础   |       1 |
+----+--------------+---------+
1 row in set (0.00 sec)

mysql> select * from lab;
+----+-----------------------+-----------+
| id | name                  | course_id |
+----+-----------------------+-----------+
|  1 | 简单的 CRUD 操作       |         1 |
+----+-----------------------+-----------+
1 row in set (0.00 sec)

删除 User 的实例 u ：

session.delete(u)
session.commit()

此时数据库状态，u 和 c 都没了，l 还在
也就是说，只在 ForeignKey 里设置 ondelete='CASCADE' 不行
还得在 relationship 里设置 cascade='all, delete-orphan' 才能实现级联删除：

mysql> select * from user;
Empty set (0.00 sec)

mysql> select * from course;
Empty set (0.00 sec)

mysql> select * from lab;
+----+-----------------------+-----------+
| id | name                  | course_id |
+----+-----------------------+-----------+
|  1 | 简单的 CRUD 操作       |      NULL |
+----+-----------------------+-----------+
1 row in set (0.00 sec)