数据库操作中,级联删除(Cascade)是一种处理数据表之间关联数据的方式。用于在删除主表(父表)中的一条记录时,自动删除与该条数据相关联的子表内的数据。
1. 适用数据库
级联删除(Cascade Delete)适用于多个主流的关系数据库管理系统(RDBMS):
1) PostgreSQL:
支持级联删除,使用 ON DELETE CASCADE 在定义外键时指定。
当父表中删除记录时,所有在子表内与之关联的记录也会被自动删除。
2) MySQL
支持级联删除,使用 ON DELETE CASCADE 在外键定义时被指定。
注意:MySQL中的 InnoDB 引擎支持级联删除,但MyISAM 引擎并不支持外键约束,故不支持级联删除。
3) Orcla
支持级联删除,使用 ON DELETE CASCADE。
当删除父表中的记录时,Orcla 会 自动删除子表的对应的记录。
4) SQL Server
支持级联删除,使用 ON DELETE CASCADE。
可以创建或修改外键时指定级联删除行为。
5) SQLite
支持级联删除,但需在启用外键约束的情况下使用。
使用 PRAGMA foreign_keys = on; 启用外键约束后,可以使用 ON DELETE CASCADE。
6) MariaDB
作为MySQL的一个分支,MariaDB也支持级联删除,方式与 MySQL 类型。
7) DB2(IBM)
支持级联删除,使用 ON DELETE CASCADE。
在DB2中,可以在外键约束中指定级联删除规则。
2. 使用场景
使用场景:
级联删除在数据库设计,有明确的父子表关系场景中;如订单及其项、用户及其日志记录等。在这些场景时,级联删除可以确保在删除父表中的记录时,不会遗留无用的子表记录,从而保持数据的一致性和完整性。
3. 实现方式
级联删除可以通过两种方式实现:
1) 使用事务手动实现级联删除
在事务中执行多个 delete 语句,根据外键关系依次删除相关的记录。
这种方法需要明确控制删除顺序,确保在删除父记录前先删除相关的子记录,以避免外键约束的冲突。
创建表 和 外键关系:(orders-父表 和 order_items-子表;从表中的每条数据与父表的单条数据关联)。
-- 创建父表 orders CREATE TABLE orders ( id SERIAL PRIMARY KEY, -- 自增主键 order_date DATE NOT NULL, customer_name VARCHAR(255) NOT NULL ); -- 创建子表 order_items CREATE TABLE order_items ( id SERIAL PRIMARY KEY, -- 自增主键 order_id INT NOT NULL, -- 外键,引用 orders 表 product_name VARCHAR(255) NOT NULL, quantity INT NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) -- 定义外键约束 );
删除 orders 表内的数据与子表所有与之关联的 order_item的数据。
顺序:必须先删除子表的记录,然后再删除父标的记录。
事务:确保操作是原子性的,要么全部成功,要么全部失败,以确保数据的一致性。
SQL是这样的:
BEGIN; -- 开始事务 -- 删除子表 order_items 中与 order_id = 1 关联的所有记录 DELETE FROM order_items WHERE order_id = 1; -- 删除父表 orders 中 id = 1 的记录 DELETE FROM orders WHERE id = 1; COMMIT; -- 提交事务
使用 Mybtis 时 xml 文件执行:
<delete id="deleteOrderWithItems"> <!-- 删除子表 order_items 中与 order_id 关联的所有记录 --> DELETE FROM order_items WHERE order_id = #{id}; <!-- 删除父表 orders 中 id 的记录 --> DELETE FROM orders WHERE id = #{id}; </delete>
2) 使用数据库的 CASCADE 规则
在定义外键约束时,可以指定 on delete cascade,当父记录被删除时,数据库会自动删除所有关联的子记录。
这种方法是数据库层面的一种自动化处理方式,无需手动编写多个删除语句。
创建表 和 外键关系:(orders-父表 和 order_items-子表;从表中的每条数据与父表的单条数据关联)。
-- 创建父表 orders CREATE TABLE orders ( id SERIAL PRIMARY KEY, -- 自增主键 order_date DATE NOT NULL, customer_name VARCHAR(255) NOT NULL ); -- 创建子表 order_items,并定义外键,指定 on delete cascade CREATE TABLE order_items ( id SERIAL PRIMARY KEY, -- 自增主键 order_id INT NOT NULL, -- 外键,引用 orders 表 product_name VARCHAR(255) NOT NULL, quantity INT NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE -- 定义外键约束,启用级联删除 );
order_items-子表 外键 order_id 引用 orders-父表 id。
SQL是:
-- 删除 orders 表中 id = 1 的记录 DELETE FROM orders WHERE id = 1;
mxl文件语法:
<!-- 删除 orders 表中 id = ... 的记录 --> <delete id="deleteOrderById"> delete from orders where id = #{id}; </delete>
定义了外键时使用了 'ON DELETE CASCADE',数据库会自动删除 '从表' 对应的 'order_id' 的数据。
4. ON DELETE外键
外键(Froreign Key)是一种数据库约束,用于在两个表之间建立关系。外键通常用于与另一个表的主键(Primark Key),并确保引用完整性,保证外键列中的值必须在被引用的表中存在。
外键约束多种类型的规则和选项,可以控制在发生数据变更时的行为。
CASCADE |
当主表的一条记录被删除时,所有引用该记录的子表中的记录也会被自动删除。 场景:当删除某条记录时,必须同时删除与该记录关联的所有的子记录(如订单与订单项)。 |
SET NULL |
当主表的一条记录被删除时,子表中引用该记录的外键字段会被设置为 NULL。 场景:当删除主记录时,子记录可以保留,但需要设置外键为 NULL(如文章和作者,删除作者但文章继续显示但不显示作者)。 |
RESTRICT |
机制删除主表中的记录,如果该记录被子表引用,则无法删除。 场景:严格禁止删除有依赖关系的主记录,确保所有关联的子记录先被处理。 |
NO ACTION |
类似于 'RESTRICT',但在某些数据库系统中,它的行为取决于其他触发器或约束。 场景:用于不想执行任何级联操作的场合。 |
SET DEFAULT |
当主表的一条记录被删除时,子表引用该记录的外键字段会被设置为预定义的默认值。 场景:当删除主记录时,子记录保留,但外键字段设置为默认值(如产品分类,删除分类时,产品的分类字段这是为默认分类)。 |
5. 注意事项
性能影响:在大量数据删除时,级联删除可能会影响性能,尤其在子表记录很多的情况下。
数据丢失风险:级联删除会自动删除与其相关的子表记录,因此慎重使用,防止意外丢失重要的数据。
外键约束:需要注意数据库引擎是否支持外键约束(如MySQL的MyISAM引擎不支持)。
标签:级联,RDBMS,删除,记录,数据库,外键,order,id From: https://www.cnblogs.com/warmNest-llb/p/18393695