Query goal: | Referential degree of a schema is defined as the number of foreign keys in the database schema. |
Notes about the query: | The query returns information about all the non-system schemas, 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 s.schema_name AS schema, COUNT(tc.table_schema) AS number_of_foreign_keys FROM information_schema.schemata AS s LEFT JOIN (SELECT table_schema FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY') AS tc ON s.schema_name=tc.table_schema WHERE schema_name NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) GROUP BY s.schema_name ORDER BY COUNT(tc.table_schema) DESC, s.schema_name; |
Collection name | Collection description |
---|---|
Find problems about integrity constraints | A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview |
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 |
---|---|
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. |