Post reply

Referential Integrity Circles...

  • August 14, 2007 at 8:23 am

    #176274

    Hi,

    I've got a database that contains two main tables contacts and entities. Entities has multiple references to contacts (contacts is a chunk of contact information), but I can't maintain them through foreign keys with update cascade/delete cascade as there are many foreign keys referencing the same tables.

    What is the recommended way to ensure that referential integrity is maintained? 

    Kind Regards,

    Jamie

     

  • August 14, 2007 at 8:30 am

    #726798

    using triggers

    Everything you can imagine is real.

  • August 14, 2007 at 8:42 am

    #726808

    Would you suggest dropping all existing foreign key constraints in order to maintain referential integrity for this situation? I had tried using a mixed approach but as entities and contacts are used in many other tables I can't use an INSTEAD OF UPDATE trigger on either table?

  • August 15, 2007 at 8:34 am

    #727053

    I suggest that you keep the RI, but not the On Delete or On update.  Handle that through the application. 

  • August 15, 2007 at 9:36 am

    #727100

    Agree with Bob. Keep the RI, prevents errors with triggers as code changes.

  • August 15, 2007 at 1:32 pm

    #727171

    Thanks for the replies. It makes sense - I was hoping there was an all encompassing solution that would allow this within SQL, but this will work going forwards. 

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply