Re: How to find out whether a row is currently referenced by a row in a different table?

<slapo(at)centrum(dot)sk> writes:
> does anyone know how to find out whether a row is currently referenced by a row in a different table?They are bound together by a foreign key constraint.
> The reason why I would like to know this is that I would like to be able to delete rows only if they aren't used anywhere.

Well, you could try to delete the row and see if it succeeds ...

> It occurred to me that I could create a function that would query a system table for all references to the table the row would be deleted from and then scan tables that refer to that one, but I was hoping there would be a simpler and maybe a more efficient way to do this.

This is a good recipe for shooting yourself in the foot. You can *not*
rely on scanning dependent tables for matching rows, because any attempt
to do that will miss just-inserted rows that haven't been committed yet.
(The FK mechanism has some special interlocking abilities to deal with
race conditions like that, but you can't get at those from SQL.)

The only way you could reliably do it "manually" would be to lock all
the dependent tables against modifications, which would pretty much
destroy any concurrency you might have.

regards, tom lane