How to seek foreing key violations on whole database? (I'm currently using MySQL)

Question

Sometimes we load data from a lot of sources, like files, streams, etc. Well ... to do it with low headaches, we usually disable foreign keys checking, the problem is that when we do get some foreign key constraint violations that we are not warned of that. How can we check this after restoring foreign key constraints?

Answer 1

Enabling the foreign key constraint will check all relations, so if there is something wrong, you will get an error.

Answer 2

there is no built-in way to do this. the only thing i can think of would be to look at the TABLE_CONSTRAINTS and KEY_COLUMN_USAGE tables in the INFORMATION_SCHEMA database to manually check for rows that don't match.

Answer 3

"Turning on" the FK after the load should indeed do the check already.

If your DBMS doesn't do that, dump it.

If your DBMS doesn't do that and you still want to keep working with such crap, you could do a query of the appropriate SEMIMINUS expression of the RA.

This is likely to look something like :

SELECT ... FROM table_with_FK WHERE NOT EXISTS ( SELECT ... FROM table_with_PK WHERE PK_attribute1 = FK_attribute1 and PK_attribute2 = FK_attribute2 and ... ) AND <anything here that allows you to identify the loaded rows>

or a bit more modern (if your DBMS supports EXCEPT) :

SELECT FK_attributes FROM table_with_FK WHERE <anything here that allows you to identify the loaded rows> EXCEPT SELECT PK_attributes_possibly_renamed FROM table_with_PK ;

EDIT (answering to "not everyone needs oracle and IBM sized products. "dump it" is not good advice.")

The OP has very clearly indicated that he is DEFINITELY interested in data integrity. So he really should be using a DBMS product that DOES offer a bit of professional-level support for ensuring data integrity. I sincerely hope that "Oracle and IBM sized products" are NOT the only ones who do that.

Answer 4

It sounds like you could basically reword your question as "How can I ensure referential integrity with foreign keys disabled?"

I imagine the very "headaches" that made you disable the foreign keys are very thing they were intended to enforce. So the simplest answer to me seems to not disable them in the first place. Do it right the first time and you won't have to do it again later.