Database Foreign key constraints vs Application Maintained Constraints

From: peter bell <>
Date: Mon, 20 Apr 2009 19:53:51 +0000
Message-ID: <COL119-W848A34FCAB5377893C6EC5D3760_at_phx.gbl>


I'm looking for thoughts / feedback / experiences of :  

  • applications that leave enforcement of foreign key relationships to the application (or simply assume that those relationships will not be violated) in an OLTP system
  • any considerations when creating foreign key constraints between tables in different schemas.

The background to this request follows below for those interested.  


Background :  

Our company is redesigning its J2EE OLTP application. One objective is to 'break up' the application into smaller loosely coupled 'sub-systems'.  

The proposal is that the 'break up' extend to database level with each sub-system having a seperate database schema.  

Some tables (such as the Customer table) will naturally have one-to-many relationships with tables in many or all of the sub-system schemas. I'll refer to the schema containing these common objects as the 'master' schema.  

All master and sub-system schemas are in the same database instance.  

I had assumed that the relationships between master and sub-system schemas would be maintained with cross-schema foreign key constraints. However, the development team have proposed we 'relax' this and not have any foreign key constraints between master and sub-system schemas.  

Some of the reasons put forward for this are :  

  • allows teams responsible for each sub-system to develop and refactor their schema freely / independently of others (without the need for the master schema)
  • reduces the potential for sub-systems to block / lock records in the master schema (and presumably vice versa)
  • since master and sub-systems always interact via some middle tier java api using globally distributed (XA) transactions and because we never delete records in the master schema, data integrity violations should not occur

My initial reactions are :  

  • Some blocking / locking is necessary if you want data integrity
  • I dont see how globally distributed transactions would necessarily reduce that or FK's increase it. Is it true to say that a foreign key constraint still allows
  • deletion of the child table records without blocking / locking the master
  • update of the child table without blocking / locking the master
  • insert into a child table without blocking / locking the master
  • update any column in the master EXCEPT the primary key without blocking / locking the child table rows


Obviously, deletion of the master record could block or be blocked by the sub-system. Likewise, updating the primary key of the master table could block or be blocked. But we should never do either of these.  

On the face of it, I can only see up sides to using foreign keys (data integrity with a simple declarative foreign key constraint). Without them, transactions inside a sub-system / or master schema could cause 'constraint' violations - there would be nothing to stop a sub-system schema adding a record with a non-existent customer_id (however, the fact that deletions from the master are outlawed may mean this is unlikely).  

It looks like we will have a handful of sub-system schemas (between 5 and 10).  

Share your photos with Windows Live Photos Free. Received on Mon Apr 20 2009 - 14:53:51 CDT

Original text of this message