Autor | Zpráva | ||
---|---|---|---|
Jan Tvrdík Profil |
#1 · Zasláno: 12. 11. 2010, 13:13:46 · Upravil/a: Jan Tvrdík
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 * |
#2 · Zasláno: 12. 11. 2010, 14:10:56
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 |
#3 · Zasláno: 12. 11. 2010, 16:12:41
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).
|
||
Časová prodleva: 3 roky
|
|||
Jan Tvrdík Profil |
#4 · Zasláno: 22. 6. 2013, 19:04:03
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. |
||
Časová prodleva: 11 let
|
0