Database Foreign key constraints vs Application Maintained Constraints
- From: peter bell <bellpk@xxxxxxxxxxx>
- To: l oracles <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 20 Apr 2009 19:53:51 +0000
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.
thanks,
peter
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.
http://clk.atdmt.com/UKM/go/134665338/direct/01/--
//www.freelists.org/webpage/oracle-l
Other related posts:
