Find Foreign Key Constraint Column Information Between Tables

Question

Table A has a foreign key constraint (type) to Table B (id). However, type is not null and id is nullable.

I'm trying to build a query using information_schema that will look at foreign key constraints and match up the column types and nullable columns to see if they sync, however I'm having problems with the logic.

select kcu.table_name, kcu.column_name, c.column_type, c.is_nullable,kcu.referenced_table_name, kcu.referenced_column_name,c.column_type, c.is_nullable
from key_column_usage kcu
inner join columns c on c.table_schema=kcu.table_schema and c.column_name=kcu.column_name and c.table_name=kcu.table_name
where kcu.referenced_table_name='Table_B' and kcu.table_name='Table_A';

I know this syntax is incorrect -- this is just all I've been able to put together so far. I'd like to have this be able to be executed for every table in a database and have it ordered by table_name, then column_name. It can exclude columns where column_type and is_nullable fields are identical.

Rowboat

Answer 1

There may be legitimate reasons for a NULLABLE column on one side of a foreign constraint, but this will compare the type/nullable properties of the columns involved.

SELECT
       kcu.constraint_schema
     , kcu.constraint_name
     , kcu.referenced_table_name
     , kcu.referenced_column_name
     , kcu.table_name
     , kcu.column_name
     , refcol.column_type referenced_column_type
     , childcol.column_type
     , refcol.is_nullable referenced_is_nullable
     , childcol.is_nullable

FROM information_schema.key_column_usage kcu
INNER JOIN information_schema.columns refcol
        ON refcol.table_schema = kcu.referenced_table_schema 
       AND refcol.table_name   = kcu.referenced_table_name
       AND refcol.column_name  = kcu.referenced_column_name 
INNER JOIN information_schema.columns childcol
        ON childcol.table_schema = kcu.table_schema 
       AND childcol.table_name   = kcu.table_name
       AND childcol.column_name  = kcu.column_name 

WHERE (
        refcol.is_nullable <> childcol.is_nullable
      OR
        refcol.column_type <> childcol.column_type
      )
AND kcu.TABLE_SCHEMA = 'rextester' #change this value to suit
ORDER BY
       kcu.table_name
     , kcu.column_name
;

See a working example (click the run button)

Pourboire

refcol.column_type <> childcol.column_type - I think that shouldn't be possible. – Diplomatist

@Paul Spiegel regarding mismatched data types the condition was requested. – Pourboire