Generic FK Reference Counting RRS feed

  • Question

  • Dear all,

    I would like to know if there is a generic way to count references to a table row that other table's columns have FK relationships to. The purpose is to know whether the referred row can be deleted without violating any FK constraints.

    Suppose I just have one table referring to the primary table, and assuming that reftable.pritableID has a FK constraint to pritable.ID, I could do the following check:

    DELETE FROM pritable WHERE ID = @ID AND (SELECT COUNT(*) FROM reftable WHERE reftable.pritableID = @ID) = 0   

    In this way, I would avoid raising an exception (from ADO.NET), I could also do the SELECT COUNT(*) prior to issuing the DELETE command to show a specific error message.

    Since I am only interested in whether the total count is zero or not, the problem with this approach is twofold:

    (1) Each table needs to be added separately into the clause
    (2) Maintenance becomes an issue if the table has many references

    Is there an alternative way to count FK row references that only requires me to supply the ID of the row and the table name? Perhaps in the format of a two-column result table (referring table name + number of references).

    Monday, January 29, 2007 1:55 AM