November 29th, 2012, 11:35 PM
How to create a foreign key that is not enforced?
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.
November 30th, 2012, 02:53 AM
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.
Tips on how to ask better questions:
November 30th, 2012, 12:00 PM
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!