Kontrola integrity dat v InnoDB tabulce

delimiter //

drop procedure if exists p_check_fk_constraint_violations
//

create procedure p_check_fk_constraint_violations(
    p_schema_name_pattern      varchar(64)
,   p_table_name_pattern       varchar(64)
)
reads sql data
sql security invoker
comment 'Finds violated Foreign Key Constaints.'
begin
    -- current schema name
    declare v_schema_name varchar(64) 
        default coalesce(p_schema_name_pattern,'%');
    -- current table name
    declare v_table_name varchar(64)
        default coalesce(p_table_name_pattern,'%');
    -- current foreign key constraint name
    declare v_constraint_name varchar(64);
    -- SQL SELECT-expression that retrieves the violating set
    declare v_statement_text text;
    -- number of foreign key constraints checked so far
    declare v_count_constraints int unsigned 
        default 0;
    -- number of foreign key constraints found to be violated so far
    declare v_count_violated_constraints int unsigned 
        default 0;
    -- number of violating records found so far
    declare v_count_violations int unsigned 
        default 0;
    -- cursor loop constrol variable
    declare v_done bit default false;
    -- This cursor yields one row per foreign key constraint.
    -- Only foreign keys found in tables specified by the
    -- p_schema_name_pattern and p_table_name_pattern 
    -- are checked.

    declare csr_fk_constraint 
    cursor for
        select      con.table_schema
        ,           con.table_name
        ,           con.constraint_name
        ,           concat(
                          'from        `'
                    ,     con.table_schema
                    ,     '`.`'
                    ,     con.table_name
                    ,     '` c'
                    ,'\n','left join   '
                    ,     max(
                              concat(
                                 '`'
                              ,  concol.referenced_table_schema
                              ,  '`.`'
                              ,  concol.referenced_table_name
                              ,  '`'
                              )
                          )
                    ,     ' p'
                    ,'\n','on          '
                    ,     group_concat(
                              concat(
                                  'c.'
                              ,   concol.column_name
                              ,   '='
                              ,   'p.'
                              ,   concol.referenced_column_name
                              )
                              order by concol.ordinal_position
                              separator '\nand         '
                          )
                    ,'\n','where       p.'
                    ,     max(concol.referenced_column_name)
                    ,     ' is null'
                    ,     if (max(col.is_nullable)='YES'
                          ,   group_concat(
                                  if( col.is_nullable = 'YES' 
                                  ,   concat(
                                          '\nand         c.'
                                      ,   col.column_name
                                      ,   ' is not null'
                                      )
                                  ,   ''
                                  )
                              )
                          ,   ''
                          )
                    ) as statement_text
        from        information_schema.table_constraints con
        inner join  information_schema.key_column_usage  concol
        on          con.constraint_schema = concol.constraint_schema
        and         con.constraint_name   = concol.constraint_name
        inner join  information_schema.columns           col
        on          concol.table_schema   = col.table_schema
        and         concol.table_name     = col.table_name
        and         concol.column_name    = col.column_name
        where       con.table_schema   like v_schema_name
        and         con.table_name     like v_table_name
        and         con.constraint_type   = 'FOREIGN KEY'
        group by    con.table_schema
        ,           con.table_name
        ,           con.constraint_name
    ;
    declare continue handler for not found 
        set v_done := true
    ;
    
    open csr_fk_constraint
    ;
    fk_loop: loop
        fetch csr_fk_constraint 
        into  v_schema_name
        ,     v_table_name
        ,     v_constraint_name
        ,     v_statement_text
        ;
        if v_done then
            leave fk_loop
            ;
        end if
        ;
        set @statement_text := concat(
            'set @count_fk_violations := '
        ,   '(select count(1) ',v_statement_text,')'
        );
        prepare stmt from @statement_text
        ;
        set @count_fk_violations := 0
        ;
        execute stmt
        ;
        deallocate prepare stmt
        ;
        set v_count_constraints := v_count_constraints + 1
        ;
        if @count_fk_violations != 0 then
            set v_count_violated_constraints := v_count_violated_constraints + 1
            ;
            set v_count_violations := v_count_violations + @count_fk_violations
            ;
            select  concat(
                        @count_fk_violations
                    ,   ' violations found for foreign key '
                    ,   '`',v_constraint_name,'`'
                    ,    ' on table '
                    ,   '`',v_table_name,'`'
                    ,   ' in schema '
                    ,   '`',v_schema_name,'`.'
                    ,   '\n'
                    ,   'Violating set can be retrieved with the following statement:'
                    ,   '\n\n'
                    ,   'select      c.*'
                    ,   '\n'
                    ,   v_statement_text
                    ,   ';\n'
                    ) as message;
            
        end if;
    end loop;
    select  concat(
                'Checked '
            ,   v_count_constraints
            ,   ' foreign keys, found '
            ,   v_count_violated_constraints
            ,   ' violated and a total of '
            ,   v_count_violations
            ,   ' violations.'
            ) as message;
end;
//