The list of all the queries

The number of compensating actions of foreign key constraints

Query goal: Find the number of compensating actions that are specified in case of foreign key constraints.
Notes about the query: If the compensatory action has not been used in case of any foreign key constraint, then it is not presented in the result.
Query type: Sofware measure (Numeric values (software measures) about the database)
Query license: MIT License
Data source: system catalog only
SQL query: Click on query to copy it

WITH compensating AS (SELECT CASE WHEN confupdtype='a' THEN 'ON UPDATE NO ACTION'
WHEN confupdtype='r' THEN 'ON UPDATE RESTRICT'
WHEN confupdtype='c' THEN 'ON UPDATE CASCADE'
WHEN confupdtype='n' THEN 'ON UPDATE SET NULL'
WHEN confupdtype='d' THEN 'ON UPDATE SET DEFAULT' END AS compensating_action
FROM pg_constraint AS o INNER JOIN pg_class AS c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS np ON c.relnamespace=np.oid
INNER JOIN pg_authid AS a ON np.nspowner=a.oid
WHERE (np.nspname='public' OR rolname<>'postgres')
AND contype='f' 
UNION ALL SELECT CASE WHEN confdeltype='a' THEN 'ON DELETE NO ACTION'
WHEN confdeltype='r' THEN 'ON DELETE RESTRICT'
WHEN confdeltype='c' THEN 'ON DELETE CASCADE'
WHEN confdeltype='n' THEN 'ON DELETE SET NULL'
WHEN confdeltype='d' THEN 'ON DELETE SET DEFAULT' END AS compensating_action
FROM pg_constraint AS o INNER JOIN pg_class AS c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS np ON c.relnamespace=np.oid
INNER JOIN pg_authid AS a ON np.nspowner=a.oid
WHERE (np.nspname='public' OR rolname<>'postgres')
AND contype='f')
SELECT compensating_action, Count(*) AS number_of_occurrences
FROM compensating
GROUP BY compensating_action
ORDER BY compensating_action;

Collections where the query belongs to

Collection nameCollection description
Find problems about integrity constraintsA 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 overviewQueries 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 databaseQueries that return numeric values showing mostly the number of different types of database objects in the database

Categories where the query belongs to

Category nameCategory description
Comfortability of data managementQueries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Compensating actionsQueries of this category provide information about compensating actions of foreign key constraints.
Relationships between tablesQueries 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.

The list of all the queries