删除外键未引用的所有记录 [英] Delete all records NOT referenced by Foreign Keys

问题描述

我想在我的桌子上运行清理命令

消除我不再在数据库中使用的行。


我想做这样的事情：


DELETE FROM tablename

WHERE IS_REFERENCED_BY_FOREIGN_KEY是假的;


有谁知道这样的事情怎么可能在PostgreSQL中完成？
？我知道我可以搜索所有表格

我知道参考这张表，看看我的主键是否存在
，但我想要一个不需要我的解决方案

每次将新的外键约束

添加到数据库时重写我的代码。


必须有办法请询问PostgreSQL在给定行或某事物上的参考数量

。


谢谢，


Dante


---------

D. Dante Lorenso
da *** @ lorenso.com


-------------------- -------（广播结束）---------------------------

提示8：解释分析是你的朋友

解决方案

2003年12月13日星期六02:20:15 -0600，

D。 Dante Lorenso <哒*** @ lorenso.com>写道：

我想在我的桌子上运行一个清理命令来消除我在数据库中不再使用的行。
我想做这样的事情：

DELETE FROM tablename
WHERE IS_REFERENCED_BY_FOREIGN_KEY是假的;

有谁知道这样的事情是如何完成的
在PostgreSQL中？我知道我可以搜索我知道的所有表格参考这个表格，看看我的主键是否存在，但是我想要一个不需要我的解决方案
每次都重写我的代码时间新的外键约束
被添加到数据库中。

必须有一种方法可以向PostgreSQL询问给定行上的引用计数
。

如果你更关心灵活性而不是速度你可以做点什么

如下：


将所有外键引用设置为所需的表，以在删除限制子句中使用

。


让您的应用程序读取所有关键值所需的表

并且对于每个密钥问题在其自己的事务中删除该密钥。

这对于引用的密钥（由于restrict子句）将失败。 br />

一个更复杂，更少的未来证明，但更有效的方法将是b $ b b我们的应用程序通过查看系统目录找出哪些表引用了感兴趣的
表，然后使用appropiate写一个

删除查询，其中不存在子句。 />

---------------------------（广播结束）-------- -------------------

提示7：别忘了增加你的免费空间地图设置

Bruno Wolff III写道：

2003年12月13日星期六02:20:15 -0600，
D. Dante Lorenso <哒*** @ lorenso.com>写道：

我想在我的表上运行一个清理命令来消除我不再在数据库中使用的行。

我想做这样的事情：

DELETE FROM tablename
WHERE IS_REFERENCED_BY_FOREIGN_KEY是假的;

有谁知道怎么回事这样可以在PostgreSQL中完成吗？我知道我可以搜索我知道的所有表格参考这个表格，看看我的主键是否存在，但是我想要一个不需要我的解决方案
每次都重写我的代码时间新的外键约束
被添加到数据库中。

必须有一种方法可以询问PostgreSQL在给定行上的引用计数。
>
如果您更关心灵活性而不是速度，您可以执行以下操作：

将所有外键引用设置为所需的表以使用
让您的应用程序读取所需表中的所有键值
，并为每个键发出在自己的事务中删除该键的密钥。
对于引用的键（由于restrict子句），这将失败。

这是非常难看的事情，这是我不得不诉诸的除非

我能找到更清洁的东西。理想情况下，我可以在插入表格后对表格数据的子集运行此清理

。不过我希望

查询速度快。


有没有人知道是否有任何方式可以说：


DELETE FROM tablename

IGNORE ERRORS;


如果执行了可能的删除但是抛出了引用的删除

诚信声音会在没有放弃整个

交易的情况下默默地失败吗？


我的外键上已经有''删除限制'条款。

更复杂，更少的未来证明，但更有效的方法是让您的应用程序通过查看哪些表格找到感兴趣的表格的参考。系统目录，然后写一个
删除查询使用适当的地方不存在的条款。

----------------------- ----（广播结束）---------------------------
提示7：别忘了增加你的免费空间地图设置

------------------- --------（广播结束）---------------------------

提示4 ：不要杀'-9''邮政局长

2003年12月13日星期六下午09:48:16 -0600，D Dante Lorenso写道：

这确实非常难看，除非我能找到更干净的东西，否则我将不得不求助于此。理想情况下，我可以在插入表格后对表格数据的子集运行此清理。不过，我希望查询能够快速完成。


怎么样：


从a.id不在的地方删除（从b中选择id）;


或等效的存在查询。

-

Martijn van Oosterhout< kl ***** @ svana.org> http://svana.org/kleptog/ （......已经离开了在任何地方，即使是开发商也几乎无法使用，大约20％完成。很好。最后的80％通常需要20％的时间，对吧？） - Anthony Towns，debian- devel-announce

-----开始PGP签名-----

版本：GnuPG v1.0.6（GNU / Linux ）

评论：有关信息，请参阅 http://www.gnupg.org


iD8DBQE / 3AB0Y5Twig3Ge + YRAtAlAJ9u8mCreNIpr / Tk69PINKTbHdE + awCfVuoC

r9kWIkzslXKFGiIkBeu00A =

= 7P25

----- END PGP SIGNATURE -----

I''d like to run a clean up command on my tables to
eliminate rows that I''m no longer using in the database.

I want to do something like this:

DELETE FROM tablename
WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE;

Does anyone know how something like this could be done
in PostgreSQL? I know I can search all the tables that
I know refer to this table and see if my primary key
exists, but I want a solution that does not require me to
rewrite my code every time a new foreign key constraint
is added to the database.

There must be a way to ask PostgreSQL for a reference count
on a given row or something.

Thanks,

Dante

---------
D. Dante Lorenso
da***@lorenso.com


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

解决方案

On Sat, Dec 13, 2003 at 02:20:15 -0600,
"D. Dante Lorenso" <da***@lorenso.com> wrote:

I''d like to run a clean up command on my tables to
eliminate rows that I''m no longer using in the database.

I want to do something like this:

DELETE FROM tablename
WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE;

Does anyone know how something like this could be done
in PostgreSQL? I know I can search all the tables that
I know refer to this table and see if my primary key
exists, but I want a solution that does not require me to
rewrite my code every time a new foreign key constraint
is added to the database.

There must be a way to ask PostgreSQL for a reference count
on a given row or something.

If you are more concerned about flexibility than speed you can do something
like the following:

Set all of your foreign key references to the desired table to use an
on delete restrict clause.

Have your application read all of the key values from the desired table
and for each key issue a delete of that key in its own transaction.
This will fail for keys that are referenced (because of the restrict clause).

A more complicated, less future proof, but more efficient approach would
be to have your application find out which tables have references to the
table of interest by looking at the system catalog and then write a
delete query using appropiate where not exist clauses.

---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

Bruno Wolff III wrote:

On Sat, Dec 13, 2003 at 02:20:15 -0600,
"D. Dante Lorenso" <da***@lorenso.com> wrote:

I''d like to run a clean up command on my tables to
eliminate rows that I''m no longer using in the database.

I want to do something like this:

DELETE FROM tablename
WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE;

Does anyone know how something like this could be done
in PostgreSQL? I know I can search all the tables that
I know refer to this table and see if my primary key
exists, but I want a solution that does not require me to
rewrite my code every time a new foreign key constraint
is added to the database.

There must be a way to ask PostgreSQL for a reference count
on a given row or something.
If you are more concerned about flexibility than speed you can do something
like the following:

Set all of your foreign key references to the desired table to use an
on delete restrict clause.

Have your application read all of the key values from the desired table
and for each key issue a delete of that key in its own transaction.
This will fail for keys that are referenced (because of the restrict clause).

This is something very ugly indeed and is what I''ll have to resort to unless
I can find something cleaner. Ideally, I would be able to run this cleanup
on a subset of the table data after an insert into the table. I would like
the query to be fast, though.

Does anyone know if there is any way to say something like:

DELETE FROM tablename
IGNORE ERRORS;

Where a delete that is possible is performed but ones that throw referencial
integrity voilations would silently fail without abandoning the entire
transaction?

I have the ''on delete restrict'' clause on my foreign keys already.
A more complicated, less future proof, but more efficient approach would
be to have your application find out which tables have references to the
table of interest by looking at the system catalog and then write a
delete query using appropiate where not exist clauses.

---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

On Sat, Dec 13, 2003 at 09:48:16PM -0600, D. Dante Lorenso wrote:

This is something very ugly indeed and is what I''ll have to resort to unless
I can find something cleaner. Ideally, I would be able to run this cleanup
on a subset of the table data after an insert into the table. I would like
the query to be fast, though.
What about just:

delete from a where a.id not in (select id from b);

or the equivalent exists query.
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/3AB0Y5Twig3Ge+YRAtAlAJ9u8mCreNIpr/Tk69PINKTbHdE+awCfVuoC
r9kWIkzslXKFGiIkBeuj0iA=
=7P25
-----END PGP SIGNATURE-----

这篇关于删除外键未引用的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！