Thread: How to find out whether a row is currently referenced by a row in a different table?
Hello everyone, does anyone know how to find out whether a row is currently referenced by a row in a different table? They are bound togetherby 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.If this can't be done by a single query, does anyone have such a function they could share or at least a few hintswhere I could look for more information? It occurred to me that I could create a function that would query a system table for all references to the table the rowwould be deleted from and then scan tables that refer to that one, but I was hoping there would be a simpler and maybea more efficient way to do this. Thanks in advance. Peter
Re: How to find out whether a row is currently referenced by a row in a different table?
From
Tom Lane
Date:
<slapo@centrum.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 togetherby 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 rowwould be deleted from and then scan tables that refer to that one, but I was hoping there would be a simpler and maybea 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