Query goal: | This metric represents the number of foreign keys in a base table. |
Notes about the query: | The query returns information about all the base tables, even those that do not have foreign key constraints. In the latter case the number would be 0. |
Query type: | Sofware measure (Numeric values (software measures) about the database) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
SELECT A.table_schema, A.table_name, Count(B.table_name) AS number_of_foreign_keys FROM information_schema.tables A LEFT JOIN (SELECT table_schema, table_name FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY' ) B USING (table_schema, table_name) INNER JOIN information_schema.schemata C ON A.table_schema=C.schema_name WHERE A.table_type='BASE TABLE' AND (A.table_schema = 'public' OR C.schema_owner<>'postgres') GROUP BY A.table_schema, A.table_name ORDER BY Count(B.table_name) DESC, A.table_schema, A.table_name; |
Collection name | Collection description |
---|---|
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Find quick numeric overview of the database | Queries that return numeric values showing mostly the number of different types of database objects in the database |
Category name | Category description |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Relationships between tables | Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly. |
Reference |
---|
Piattini, M., Calero, C., Sahraoui, H. A., & Lounis, H. (2001). Object-relational database metrics. L'Objet, 7(4), 477-496. |