7

In databases where foreign key checking has been disabled in the past, how can one check for foreign key constraint violations?

sgryzko
  • 2,397
  • 2
  • 22
  • 37
Gedean Dias
  • 1,063
  • 1
  • 10
  • 24
  • 1
    I think it is better to find and handle the "headaches" before you put bad data into the database. By turning off the foreign keys to make things easier, you are making things worse and defeating the entire purpose of having a foreign key to begin with. Pre-process the data instead. Never put bad data knowingly into a database. – HLGEM Oct 05 '09 at 17:16
  • 2
    You could use http://stackoverflow.com/a/5977191/950503 to check all foreing key violations. – windm Oct 06 '12 at 09:43

5 Answers5

3

Basically, if you have no foreign key constraints, you can do this:

SELECT * FROM CHILD C WHERE C.PARENT_ID NOT IN (SELECT ID FROM PARENT);
Mike Demenok
  • 791
  • 8
  • 15
  • 2
    -1; firstly, the situation described by the OP is not one in which *"you have no foreign key constraints"*, and secondly, this ignores the "on whole database" condition in the question. – Mark Amery Oct 12 '16 at 12:20
1

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.

longneck
  • 11,938
  • 2
  • 36
  • 44
0

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.

eaolson
  • 14,717
  • 7
  • 43
  • 58
  • 3
    This isn't helpful. *"Q: How do I treat a broken arm? A: Don't break your arm."* Uh, okay, but *I have a broken arm* and that's why I'm here. – Mark Amery Oct 11 '16 at 12:37
-2

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

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • 2
    the question has the given that foreign key constraints are disabled for a reason. and turning them back on after the data is loaded won't cause mysql to check the existing data. so how is this answer useful? – longneck Oct 05 '09 at 14:29
  • 1
    Please prove that enabling the foreign keys doesn't fail in MySQL if there are violations. If that was true, foreign keys would be useless. If there is a bug, use ALTER TABLE to remove the FK constraints and then add them: When you add a FK constraint, the DB *must* check the data. – Aaron Digulla Oct 05 '09 at 14:41
  • 2
    mysql supports foreign keys and does enforce them. but it also allows you to temporarily disable enforcement of those keys to speed up data imports of relational data. see http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#sysvar_foreign_key_checks – longneck Oct 05 '09 at 14:45
  • 2
    @AaronDigulla the scenario described by the OP is not one in which he *adds* a foreign key constraint to a table with existing violations - that would indeed fail. Rather, he is describing having disabled all foreign key validation with `SET FOREIGN_KEY_CHECKS=0`, then inserted data that violated a foreign key constraint, and then re-enabled foreign key validation by setting the `FOREIGN_KEY_CHECKS` system variable back to 1. This will indeed throw no error but result in the table containing constraint-violating data - you can trivially check this at the MySQL shell. -1; this answer is wrong. – Mark Amery Oct 11 '16 at 12:55
-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.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • not everyone needs oracle and IBM sized products. "dump it" is not good advice. – longneck Oct 05 '09 at 17:08
  • @longneck: There are more DBMS products than Oracle and IBM. In fact, given that the user is already using MySQL, I would think PostgreSQL would be the next logical choice if he did dump MySQL. – Powerlord Oct 05 '09 at 17:27