Orphaned email_addr_bean_rel records

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  '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

Parents Reply Children