从一个表中删除，除非在另一个表中引用了行

Question

我们有两个表，看起来像这样：

CREATE TABLE devices(
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(255) NOT NULL,
    "last_log_id" integer NULL
);
CREATE TABLE log(
    "id" serial NOT NULL PRIMARY KEY, 
    "created_at" timestamp with time zone NOT NULL,
    "msg" varchar(255) NOT NULL,
);
ALTER TABLE "devices" ADD CONSTRAINT 
   "device_last_log_id" FOREIGN KEY ("last_log_id") 
   REFERENCES "log" ("id") DEFERRABLE INITIALLY DEFERRED;

删除早于某个"created_at"日期的所有"日志"行的有效查询是什么，除非它们被"设备"表"last_log_id"列引用？

Answer 1

delete from log l
where l.created_at < 'somedate'
and not exists (select 1
     from devices d
     where d.last_log_id = l.id
    );