Check if a value is used in multiple foreign keys

Question

Check if a value is used in multiple foreign keys

chris.king

SSCrazy

Points: 2007
More actions
July 4, 2011 at 6:19 am

#389888

Hi All,
I am currently writing a stored procedure that takes an integer value as a parameter. This value is the id of a primary key field in a table. I then need to check multiple other tables to see if the specified value is used in any foreign keys.
I can accomplish this as follows (in pseudo-code)
SELECT CASE
WHEN b.TableAID IS NOT NULL THEN 1
ELSE CASE
WHEN c.TableAID IS NOT NULL THEN 1
ELSE 0
END
END AS HasDependencies
FROM dbo.TableA a
LEFT OUTER JOIN dbo.TableB b
ON a.TableAID = b.TableAID
LEFT OUTER JOIN dbo.TableC c
ON a.TableAID = c.TableAID
WHERE a.TableAID = @TableAID
The problem is that the primary table field can be referenced by up to 10 foreign keys and I really don't want to have to do 10 nested case statements if there is a better, more efficient way I can do this.
Can anyone recommend a more efficient way to approach this?
TIA,
Chris

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

I assume that if a single dependency is missing you want the dependency-check to fail, is that correct?

If that's the case I would join the ten tables by that "FK" column, if result "EXISTS" then you know all dependencies are there.

Out of curiosity, is this really calling to check for dependencies on child tables based on a parent table value?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

chris.king SSCrazy Points: 2007 More actions · Answer 2

Hi There,

Thanks for your reply. Actually, the reverse is true.

The end requirement is to indicate, in a bit field in the results of the stored procedure, if the row that contains the passed in value can be deleted or not based on it being reference in any of the child tables.

The reason for this is that the relevant item can then be marked as deletable in an application.

Thanks,

C