• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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.
2 Solutions
Paul JacksonCommented:
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.
Asim NazirCommented:
Alpesh PatelAssistant 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


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

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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now