Generally: How can I discover referential integrity errors in MySQL?

Specifically:

I have several tables for my photo album software; for simplicity,
let's say I just have these three:

photos ( pho_id, description )
   pho_id should be unique
photos_persons ( ID, pho_id, per_id )
   ID should be unique
   pho_id should exist in photos
   per_id should exist in persons
persons ( per_id, name )
   per_id should be unique

What is the SQL which would tell me if these conditions are satisfied?

You can run a series of queries that check each condition. If any
query returns records, that condition is not satisfied.

----------------------------------

Condition: photos.pho_id should be unique

SELECT DISTINCT pho_id, COUNT(pho_id) AS NotUnique
  FROM photos
  GROUP BY pho_id
  HAVING (COUNT(pho_id) > 1);

----------------------------------

Condition: photos_persons.ID should be unique 

SELECT DISTINCT ID, COUNT(ID) AS NotUnique
  FROM photos_persons
  GROUP BY ID
  HAVING (COUNT(ID) > 1);

----------------------------------

Condition: photos_persons.pho_id should exist in photos AND
photos_persons.per_id should exist in persons

SELECT ID
FROM photos_persons
WHERE ((NOT EXISTS (SELECT pho_id FROM photos WHERE
photo_persons.pho_id = photos.pho_id)) AND (NOT EXISTS (SELECT per_id
FROM persons WHERE photo_persons.per_id = persons.per_id)));

----------------------------------

Condition: persons.per_id should be unique

SELECT DISTINCT per_id, COUNT(per_id) AS NotUnique
  FROM persons
  GROUP BY per_id
  HAVING (COUNT(per_id) > 1);


These queries may need to be adjusted depending on your version of
mySQL.
For more information, there is a nice searchable mySQL manual at
MySQL:
http://www.mysql.com/doc/en/index.html

Search strategy:
mySQL SELECT "NOT EXISTS"

Good luck with your project!

- Hammer

---

Request for Answer Clarification by placain-ga on 29 May 2003 09:03 PDT

Can you please help me with the third one? I'm using mysql 3.23.44,
which does not support subselects.

---

Clarification of Answer by hammer-ga on 29 May 2003 10:07 PDT

Try this syntax instead:

Condition: photos_persons.pho_id should exist in photos

SELECT ID 
FROM photos_persons 
LEFT JOIN photos ON photos_persons.pho_id = photos.pho_id
WHERE photos.pho_id IS NULL;

------------------------------------

Condition: photos_persons.per_id should exist in persons

SELECT ID 
FROM photos_persons 
LEFT JOIN persons ON photos_persons.per_id = persons.per_id
WHERE persons.per_id IS NULL;

mySQL Manual - Subselects
Alternative syntax
http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html


- Hammer

Great! Thanks very much.