Find Foreign Key Constraint Column Information Between Tables

information-schema, mysql, percona

Solution

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)

Problem

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.

Original source