What type of SQL constraint to use?

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.
IntuvoAsked:
Who is Participating?
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
Please create Primary key foreign key relationship between the Contact and Address table.


Contact ID Primiary key on Contact TAble
AddressID Primary key on Address table. + ContactId Foreign key on Table Address

OR

Contact ID Primiary key on Contact TAble
AddressID Primary key on Address table.
Third table Which has both Foreign key of ContactID and AddressID

0
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
You're right a foreign key constraint won't for the resons you give. I think the only way you will be able to handle this is as you suggest with a update query that checks whether there are any other contacts associated with the address record before the currentlyassociated contact is associated with another address.
However what you will need to think about is what should happen in this scenario, presumably there is a reson the contact is bening associated with another address so rather than blocking this as you suggest wouldn't the update query delete the orphaned address if there are no other contacts associated with it.
0
 
Asim NazirCommented:
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
A Foreign Key constraint in Contact referencing the Primary Key in Address perfectly meets your need.  No need for a trigger on this.  Implementing the constraint will prevent *any* possibility of a Contact being orphaned.  And yes that will meet your need to have Addresses created before Contacts.

Hope this helps...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.