Counting references to a record in a table via Foreign Keys

Total: 3 Average: 3.7

I have recently needed to solve the task for my own purpose: to calculate the number of external records linked by a foreign key for each record in a table (File). The task was solved for the specific structure of the File table, but if necessary, the solution can be reworked to a universal one.

I’ll clarify that the solution was developed for an unloaded database, without millions of records and an every minute update, so there was not much concern about the performance.

The main reason was that the number of external links to the File table could change during development and it would be simply unreasonable to constantly rewrite the query. A certain modularity was planned in the system, therefore, all final tables are not exactly known.

The script for creating two labels:

We get the File and TestForFiles tables. The TestForFiles table refers to the File table by the IdFileForTest field.

We obtain the following data set:

The script generates a query to count the number of records in the table:

The following query is generated:
After the execution, we have such table contents:

Once again, the task was solved for a specific File table, counting works only for cases when there are foreign keys on the IdFile field.

This article was translated by Codingsight team with the permission of the author.


A community platform for IT specialists