SQL:如何找到未使用的主键 [英] SQL: how to find unused primary key

问题描述

我有一个包含 > 1'000'000 个条目的表格；该表引用自大约 130 个其他表.我的问题是这些 1-mio-entries 中的很多都是旧的且未使用过.

I've got a table with > 1'000'000 entries; this table is referenced from about 130 other tables. My problem is that a lot of those 1-mio-entries is old and unused.

查找未被任何其他表引用的条目的最快方法是什么?我不喜欢做

What's the fastet way to find the entries not referenced by any of the other tables? I don't like to do a

select * from (
select * from table-a TA
minus
select * from table-a TA where TA.id in (
select "ID" from (
   (select distinct FK-ID "ID" from table-b)
union all
  (select distinct FK-ID "ID" from table-c)
...

有没有更简单、更通用的方法?

Is there an easier, more general way?

谢谢大家！

推荐答案

你可以这样做:

select * from table_a a
 where not exists (select * from table_b where fk_id = a.id)
   and not exists (select * from table_c where fk_id = a.id)
   and not exists (select * from table_d where fk_id = a.id)
   ...

这篇关于SQL:如何找到未使用的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！