What type of SQL constraint to use?

Posted on 2011-04-29
Last Modified: 2012-05-11
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.
Question by:Intuvo
    LVL 29

    Assisted Solution

    by:Paul Jackson
    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.
    LVL 10

    Expert Comment

    by:Asim Nazir
    LVL 21

    Accepted Solution

    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

    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how the fundamental information of how to create a table.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now