I'm struggling to determine the best way to force the integrity of a column in our database. Consider two tables, addresses & contacts with primary Keys ADDRID & CID respectively. They have a one to many relationship. In our logic we always create an address first and then create a contact with they ADDRID of the previously created address.
Sometimes a contact is attached to another address. I want to ensure that an address is never "orphaned". In other words the database should never allow a contact to be removed from an address if it is the only contact related to the Address.
I don't believe a foreign key constraint will work, since address records are always create first they wont always have a matching contact initially.
So would a update trigger be the best way to manage this?
Your guidance is appreciated.