Blake Rivell Blake Rivell - 3 months ago 7
SQL Question

Determining whether a foreign key value exists in any of multiple tables that have thousands of records

When the user clicks a delete button in my UI to delete a product I need to do a fast check to see if it is a foreign key in any of the 4 tables (Table1, Table2, Table3, Table4). If it isn't then I can proceed with the delete. If it is in a single one of them I can't delete it.

Some of these tables have thousands of records and I already learned the hard way that using joins is not the best way because the query takes minutes to complete.

I figured union might be the best way but I am wondering if there is a way I can further enhance it. Or even possibly send back which tables it is involved in so I can give the user a descriptive message on why they can't delete the Product.

Here is what I have so far and it is really fast, but returns thousands of 1's when the product exists all over the place. I suppose I can just do a single or default and if not null then don't let them delete.

select 1
from (
select ProductId from Table1
union all
select ProductId from Table2
union all
select ProductId from Table3
union all
select ProductId from Table4
) tbl
where ProductId = 1000

Answer

Here is a method with exists and case:

select (case when exists (select 1 from table1 where productId = 1000) then 1
             when exists (select 1 from table2 where productId = 1000) then 1
             when exists (select 1 from table3 where productId = 1000) then 1
             when exists (select 1 from table4 where productId = 1000) then 1
             else 0
        end)