No FK in db is not a good idea. It is just matter of time that some orphan records will appear in db and you do not know if they are junk data (which should not be there, but inserted accidently), or they are valid data but with parents missing. At that point, developers will not admit any "mistakes" on their part. DBA has to "fix" the issue. Talkng about first hand experience.
I don't know your reason(s) to 'break up' the application, but it seems to me that these sub-systems could stay in same schema, as long as there is no db object name conflict. Accessing different schema objects require all kinds privs granted to original schema owner. It can be done with 5-10 different sub-users but why to create this "headache"?
Guang Mei www.eversave.com----- Original Message ----- From: "peter bell" <bellpk@xxxxxxxxxxx>
To: "l oracles" <oracle-l@xxxxxxxxxxxxx> Sent: Monday, April 20, 2009 3:53 PMSubject: Database Foreign key constraints vs Application Maintained Constraints
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 occurMy 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
-- //www.freelists.org/webpage/oracle-l
