Today I came across a relationship record between email addresses and a contact bean where the contact id did not exist.
I did a quick query: (*EDIT* added checks for deleted per Harald Kuske 's suggestion)
SELECT
rel.id,
rel.date_created,
rel.date_modified,
rel.bean_module,
rel.bean_id,
rel.email_address_id,
CASE
WHEN ea.id IS NULL THEN 'Missing Email Address Record'
ELSE 'Missing Parent Bean Record'
END as orphan_reason
FROM email_addr_bean_rel rel
LEFT JOIN email_addresses ea ON rel.email_address_id = ea.id and
ea.deleted = 0
WHERE (ea.id IS NULL
OR (rel.bean_module = 'Contacts' AND rel.bean_id NOT IN (SELECT id
FROM contacts))
OR (rel.bean_module = 'Accounts' AND rel.bean_id NOT IN (SELECT id
FROM accounts))
OR (rel.bean_module = 'Leads' AND rel.bean_id NOT IN (SELECT id
FROM leads))
OR (rel.bean_module = 'Users' AND rel.bean_id NOT IN (SELECT id
FROM users))
)
and rel.deleted = 0
and I found some 120K orphaned rows.
Anyone have any idea how these would be orphaned?
I assume that deleting Contacts would also delete all the relationships to that Contact.
thanks,
Francesca