Foreign key constraint checking in MySQL can be enabled or disabled through the dynamic system variable FOREIGN_KEY_CHECKS. (see: http://dev.mysql.com/doc/refman/5.0/en/set-option.html).

Foreign key constraint checking is sometimes temporarily disabled to make it easier (and faster) to load data into a couple of related tables. Disabling foreign key checking can also be used to emulate DEFERRED foreign key constraints. Re-enabling foreign key checking does not automatically validate the constraint. 'Bad' data may have entered the database during a period of time when foreign key constraint checking was disabled.

This procedure checks for these violations and reports helpful information that may aid in restoring the database's referntial integrity. When a problem is found, a statement is suggested that retrieves the violating rows.

The procedure accepts two parameters. The p_schema_name_pattern parameter accepts a pattern that is used in a LIKE expression to identify the schemas where will be looked. If it is NULL, '%' is assumed. The p_table_name_pattern parameter identifies the tables that will be checked in the schemas identified by the p_schema_name_pattern parameter. The p_table_name_pattern parameter accepts a pattern that is used in a LIKE expression. If it is NULL, '%' is assumed.

Sample usage:

use sakila;

mysql> call p_check_fk_constraint_violations('sakila','%');
+------------------------------------------------------------------------+
| message                                                                |
+------------------------------------------------------------------------+
| Checked 22 foreign keys, found 0 violated and a total of 0 violations. |
+------------------------------------------------------------------------+
1 row in set (0.51 sec)


mysql> set foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from sakila.film;
Query OK, 1000 rows affected (0.62 sec)

mysql> call p_check_fk_constraint_violations('sakila','%');
+------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------+
| message
                                                           |
+------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------+
| 5462 violations found for foreign key `fk_film_actor_film` on table `film_actor` in schema `sakila`.
Violating set can be retrieved with the following statement:

select      *
from        `sakila`.`film_actor` c
left join   `sakila`.`film` p
on          c.film_id=p.film_id
where       p.film_id is null;
 |
+------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------+
1 row in set (0.53 sec)

+------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------+
| message
                                                                    |
+------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------+
| 1000 violations found for foreign key `fk_film_category_film` on table `film_category` in schema `sakila`.
Violating set can be retrieved with the following statement:

select      *
from        `sakila`.`film_category` c
left join   `sakila`.`film` p
on          c.film_id=p.film_id
where       p.film_id is null;
 |
+------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------+
1 row in set (0.53 sec)

+------------------------------------------------------------------------------------------------------------
--------------------------------------------------------+
| message
                                                        |
+------------------------------------------------------------------------------------------------------------
--------------------------------------------------------+
| 4581 violations found for foreign key `fk_inventory_film` on table `inventory` in schema `sakila`.
Violating set can be retrieved with the following statement:

select      *
from        `sakila`.`inventory` c
left join   `sakila`.`film` p
on          c.film_id=p.film_id
where       p.film_id is null;
 |
+------------------------------------------------------------------------------------------------------------
--------------------------------------------------------+
1 row in set (0.53 sec)

+----------------------------------------------------------------------------+
| message                                                                    |
+----------------------------------------------------------------------------+
| Checked 22 foreign keys, found 3 violated and a total of 11043 violations. |
+----------------------------------------------------------------------------+

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

Current Tags

You must be logged in to tag this tool