Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE)

For that, I have a piece of code that will give me a list of all (table, column) referencing that row's id. Then all I want to know is if any of those (table, column) have my row's id there, which would prevent its deletion.

I found 3 ways to do that, and all producing similar results on EXPLAIN ANALYZE on a very large database. I'll paste here some real examples:

The first one is to make a UNION of each (table, column) and check if the id is there (if it's null at the end, there's no references):

```
(SELECT client_category_price.sellable_id
FROM client_category_price
WHERE client_category_price.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
(SELECT commission_source.sellable_id
FROM commission_source
WHERE commission_source.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
(SELECT loan_item.sellable_id
FROM loan_item
WHERE loan_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
(SELECT product.sellable_id
FROM product
WHERE product.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
(SELECT product_history.sellable_id
FROM product_history
WHERE product_history.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
(SELECT purchase_item.sellable_id
FROM purchase_item
WHERE purchase_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
(SELECT receiving_order_item.sellable_id
FROM receiving_order_item
WHERE receiving_order_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
(SELECT returned_sale_item.sellable_id
FROM returned_sale_item
WHERE returned_sale_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
(SELECT sale_item.sellable_id
FROM sale_item
WHERE sale_item.sellable_id

I suggest your first option, with two improvements and some simplifications.

(
SELECT 1      -- irrelevant what you select here
FROM   client_category_price
WHERE  sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460'
LIMIT  1      -- may be redundant
)
UNION ALL     -- not just UNION

  ...

UNION ALL
(
SELECT 1
FROM   work_order_item
WHERE  sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460'
LIMIT  1
)
LIMIT  1;      -- this one is crucial

Given that all you want to know is


if any of those (table, column) have my row's id there, which would prevent its deletion.

-
You don't need a full list of violating rows. Stop searching at the first one. All you need to do is add another LIMIT 1 at the end of the query. This way, Postgres skips rest of the query as soon as the first row is found.
You probably don't need LIMIT 1 for each SELECT, just the one at the end. Test without, it may produce different query plans.

-
Use UNION ALL instead of UNION. Faster.

-
Some other simplifications.

Related answer on SO:

• Way to try multiple SELECTs till a result is available?

StackExchange Database Administrators Q#80524, answer score: 2