Goal This query identifies a severe logical contradiction in the database schema. It finds situations where a column is strictly defined as NOT NULL (either directly or inherited via a domain), but is simultaneously subject to a CHECK constraint that explicitly requires the value to be missing (e.g., containing an IS NULL condition). This conflict—whether applied as a single-column or multi-column constraint—creates a logical impossibility, preventing any valid data insertion into the table. The query outputs the schema name, table name, column name, domain name (if applicable), the column's mandatory status, and the definition of the problematic CHECK constraint.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA only
SQL Query
WITH table_checks AS (
    SELECT 
        ccu.table_schema,
        ccu.table_name,
        ccu.column_name,
        cc.constraint_name,
        cc.check_clause,
        'TABLE CHECK' AS check_type
    FROM information_schema.constraint_column_usage AS ccu
    INNER JOIN information_schema.check_constraints AS cc 
        ON ccu.constraint_schema = cc.constraint_schema 
       AND ccu.constraint_name = cc.constraint_name
    INNER JOIN information_schema.tables AS t 
        ON ccu.table_schema = t.table_schema 
       AND ccu.table_name = t.table_name
    WHERE t.table_type = 'BASE TABLE'
      AND cc.check_clause NOT LIKE '%IS NOT NULL'
      AND ccu.table_schema NOT IN (
          SELECT schema_name 
          FROM information_schema.schemata 
          WHERE schema_name <> 'public' 
            AND schema_owner = 'postgres' 
            AND schema_name IS NOT NULL
      )
      AND cc.check_clause ~* ('(\y|")' || ccu.column_name || '(\y|")(\s*\)*\s*::\s*[\w"]+)?\s*\)*\s+IS\s+NULL\y')

    UNION ALL
    SELECT 
        cdu.table_schema,
        cdu.table_name,
        cdu.column_name,
        cc.constraint_name,
        cc.check_clause,
        'DOMAIN CHECK' AS check_type
    FROM information_schema.column_domain_usage AS cdu
    INNER JOIN information_schema.tables AS t 
        ON cdu.table_schema = t.table_schema 
       AND cdu.table_name = t.table_name
    INNER JOIN information_schema.domain_constraints AS dc 
        ON cdu.domain_schema = dc.domain_schema 
       AND cdu.domain_name = dc.domain_name
    INNER JOIN information_schema.check_constraints AS cc 
        ON dc.constraint_catalog = cc.constraint_catalog
       AND dc.constraint_schema = cc.constraint_schema 
       AND dc.constraint_name = cc.constraint_name
    WHERE t.table_type = 'BASE TABLE'
      AND cc.check_clause NOT LIKE '%IS NOT NULL'
      AND cdu.table_schema NOT IN (
          SELECT schema_name 
          FROM information_schema.schemata 
          WHERE schema_name <> 'public' 
            AND schema_owner = 'postgres' 
            AND schema_name IS NOT NULL
      )
      AND cc.check_clause ~* '(\y|")VALUE(\y|")(\s*\)*\s*::\s*[\w"]+)?\s*\)*\s+IS\s+NULL\y'
)
SELECT DISTINCT
    c.table_schema AS schema_name,
    c.table_name AS table_name,
    c.column_name AS column_name,
    c.domain_name AS domain_name,
    c.is_nullable AS is_mandatory,
    chk.check_clause AS problematic_check
FROM table_checks AS chk
INNER JOIN information_schema.columns AS c 
    ON chk.table_schema = c.table_schema 
   AND chk.table_name = c.table_name 
   AND chk.column_name = c.column_name
WHERE c.is_nullable = 'NO'
ORDER BY schema_name, table_name, column_name;

Fixing Suggestion

To resolve this critical issue, you must eliminate the logical contradiction by reviewing the business requirements for the flagged column. If the column must genuinely hold a value, drop the conflicting CHECK constraint (or modify its boolean expression to remove the IS NULL condition). Conversely, if the column is intentionally meant to be empty or conditionally optional, remove the NOT NULL constraint from the column (or domain) and adjust the CHECK logic accordingly.

Collections

This query belongs to the following collections:

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

CHECK constraints

Queries of this category provide information about CHECK constraints.

Missing data

Queries of this category provide information about missing data (NULLs) in a database.

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.