0

Say I have record customers and record transactions. Each transaction has a customer ID field pointing to the customer record.

Obviously if you delete a customer record, you have to delete all the transactions first, to avoid dangling pointers, IDs for which the corresponding record no longer exists. But I inherited this database and I want to make sure that there were no past coding errors leaving inconsistencies in the DB.

If I want to verify that there are no dangling pointers (no customer_id values for which customers.id does not exist), what kind of query would I use?

user157426
  • 23
  • 6
  • A `LEFT JOIN` query. Google that, have a try. You might also want to have a look at foreign keys and their `DELETE CASCADE` function. – fancyPants Dec 17 '15 at 16:24

2 Answers2

0
SELECT Customer_id FROM TRANSACTION TRX
WHERE NOT EXISTS(SELECT 1 FROM Customer WHERE CustomerId = TRX.Customer_Id)
AntDC
  • 1,807
  • 14
  • 23
0

Or simply:

SELECT record_id FROM records_table WHERE customer_id NOT IN (SELECT customer_id FROM customer_table);

This will show you every record associated with a customer_id which does not exist in the customers table.

And then to remove:

DELETE FROM records_table WHERE customer_id NOT IN (SELECT customer_id FROM customer_table);
Redbeard011010
  • 954
  • 6
  • 20