lightdb 基础使用 外键ON DELETE CASCADE

级联删除

假设个订单表orders和订单明细表orders如下

CREATE TABLE orders(
    order_id varchar(64) PRIMARY KEY,
    order_comment varchar(256),
    order_status varchar(1)
);

CREATE TABLE orders_detail(
    order_id varchar(64) REFERENCES orders(order_id) ON DELETE CASCADE,
    goods_id varchar(64)
);

造一些数据

insert into orders values('1', '测试1', '1');
insert into orders_detail values('1', 'a');
insert into orders_detail values('1', 'b');
insert into orders values('2', '测试2', '2');
insert into orders_detail values('2', 'a');
insert into orders_detail values('2', 'b');

查看数据

lightdb@lt_test=# select * from orders;
 order_id | order_comment | order_status 
----------+---------------+--------------
 1        | 测试1         | 1
 2        | 测试2         | 2
(2 rows)

lightdb@lt_test=# select * from orders_detail;
 order_id | goods_id 
----------+----------
 1        | a
 1        | b
 2        | a
 2        | b
(4 rows)

删除订单

lightdb@lt_test=# delete from orders where order_id = '1';
DELETE 1
lightdb@lt_test=# select * from orders;
 order_id | order_comment | order_status 
----------+---------------+--------------
 2        | 测试2         | 2
(1 row)

lightdb@lt_test=# select * from orders_detail;
 order_id | goods_id 
----------+----------
 2        | a
 2        | b
(2 rows)

子表数据也被删除了