#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0

    How to create a foreign key that is not enforced?


    Hello,

    I am porting a SQL Server application to Postgresql. The application has a custom auditing system. For each table, there is a history table, and they are related by a foreign key constraint, but it marked as not enforced. For example, the Customer table has a CustomerHistory table. The CustomerHistory table has a FK from its CustomerId field to Customer.CustomerId. The intention is that Customers can be deleted, but their history will always remain. Is it possible to create a similar foreign key constraint in PG? I have experimented, but it seems that a FK in PG is always an active one that is enforced. I'd appreciate your suggestions.

    Thanks,
    Mike
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    What's the use in defining a foreign key that is not enforced - especially in an OLTP environment?

    The only thing that I can think of, is to define the FK as "ON DELETE SET NULL". In that case the CustomerId in the history table will be set to null if you delete the customer.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0
    Hi Shammat,

    After a good night of sleep, I awoke with the same conclusion... In the past, the customer used an application generator and for it to properly generate related tables in the user interface, it needed these FK relationships, even though they didn't have a real function. They are no longer using the application generator, so we can ignore these FKs.

    Thank you for your response!
    Mike

IMN logo majestic logo threadwatch logo seochat tools logo