• Debarun Banerjee's avatar
    BUG#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL IN INFORMATION_SCHEMA AFTER RESTART · 49edf2d4
    Debarun Banerjee authored
    Problem :
    ---------
    Information_Schema.referential_constraints (UNIQUE_CONSTRAINT_NAME)
    shows NULL for a foreign key constraint after restarting the server.
    If any dml or query (select/insert/update/delete) is done on
    referenced table, then the constraint name is correctly shown.
    
    Solution :
    ----------
    UNIQUE_CONSTRAINT_NAME column is the key name of the referenced table.
    In innodb, FK reference is stored as a list of columns in referenced
    table in INNODB_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS. The referenced
    column must have at least one index/key with the referenced column as
    prefix but the key name itself is not included in FK metadata. For this
    reason, the UNIQUE_CONSTRAINT_NAME is only filled up when the
    referenced table is actually loaded in innodb dictionary cache.
    
    The information_schema view calls handler::get_foreign_key_list() on
    foreign key table to read the FK metadata. The UNIQUE_CONSTRAINT_NAME
    information shows NULL based on whether the referenced table is
    already loaded or not.
    
    One way to fix this issue is to load the referenced table while reading
    the FK metadata information, if needed.
    Reviewed-by: default avatarSunny Bains <sunny.bains@oracle.com>
    
    RB: 14654
    49edf2d4
foreign_key.test 2.9 KB