How to look for external violations in the entire database? (I am currently using MySQL)

Question

In databases where foreign key verification has been disabled in the past, how can you verify violations of foreign key restrictions ?

Answer 1

Basically, if you do not have foreign key restrictions, you can do this:

SELECT * FROM CHILD C WHERE C.PARENT_ID NOT IN (SELECT ID FROM PARENT);

Answer 2

There is no built-in way to do this. the only thing I can think of is to look at the tables TABLE_CONSTRAINTSand KEY_COLUMN_USAGEin the database INFORMATION_SCHEMAto manually check if the rows match.

Answer 3

, " ?"

, " ", , - , . , , . , .

Answer 4

, , - , .

Answer 5

"" FK .

, .

, , SEMIMINUS RA.

, :

SELECT... FROM table_with_FK ( ... FROM table_with_PK WHERE PK_attribute1 = FK_attribute1 PK_attribute2 = FK_attribute2 ... ) < , >

( EXCEPT):

SELECT FK_attributes FROM table_with_FK WHERE < , > SELECT PK_attributes_possibly_renamed FROM table_with_PK ;

EDIT ( " IBM". "" - .)

The OP clearly indicates that it is DEFINITELY interested in data integrity. Therefore, he really should use a DBMS product that offers some professional support to ensure data integrity. I sincerely hope that Oracle and IBM-sized products are NOT the only ones doing this.