Autor Zpráva
Jan Tvrdík
Profil
V DB mám několik tabulek, které jsou vzájemně provázané přes cizí klíče. Ty zajišťují integritu nově přidaných dat, ale protože při importu dat se volá SET foreign_key_checks = 0;, může dojít k porušení integrity.

Existuje nějaký jiný způsob, jak provést kontrolu integrity než dropnout ten původní klíč a zkusit ho vytvořit znova?
Kajman_
Profil *
Třeba půjde podívat se např. přes information scheme na seznam klíčů a pak si z toho vygenerovat selecty, které vypíší nevalidní hodnoty.
Jan Tvrdík
Profil
Díky za tip. Ped mi na Nette Jabber Room poradil šikovnou proceduru, která to celé krásně automatizuje (automaticky pokládá SQL dotazy, které vyhledávají nevalidní hodnoty).
Jan Tvrdík
Profil
Odkaz umřel, proceduru lze stále vytáhnout z internetového archivu. Pro jistotu ji zkopíruji i sem:

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;
//


Alternativně lze použít od Davida Grudla mini knihovnu MySQL check.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: