Suggestion to avoid Refrential Integrity in DB2

Keanehelp · New User Joined: 27 May 2008 Posts: 71 Location: USA, CA.

Hi,

We are in a process of designing a DB2 data base for our application. While designing a DBA suggested to avoid foreign keys (refrential integrity) as it makes(as per him) data recovery very troublesome in DB2.

I also did some resarch and find out that DB2 lacks cross platform Refrential Integrity.

but I am still not convinced about this thought.

Any suggestions/comments are welcome.

GuyC · Posted: Mon Mar 15, 2010 2:35 pm

"Making data recovery troublesome" is IMHO not a good reason not to do it.
RI is a business requirement.
If you think you can fulfill that requirement by Application code and waterproof data recovery guidelines, feel free to go for it.

Keanehelp · New User Joined: 27 May 2008 Posts: 71 Location: USA, CA.

Thanks for the reply GuyC,

Could you also let me know how tough it is to recover data in case of RI based Database?

Is it a good enough reason for avoiding RI?

GuyC · Posted: Mon Mar 15, 2010 5:07 pm

Suppose you have several tables connected with RI.

It is not enough to restore one table, you might need to restore the children as well. otherwise you can get orphans.
This you might get with or without RI. The only difference is that with RI, DB2 will notice and will refuse to put the tables RW. (you can always force it)

Without RI you don't need to check or force children tables, but you'll never be 100% sure the data is correct.

In a reasonably sized data model (possibly with circular RI ), restoring a table often means restoring other tables and/or checking a lot of tables before you application is back up and running.

It depends on the application :
Is data integrity important?
Is it a volatile application ?
How important is a fast restart while restoring ?
Do you need other table being live while restoring some tables ?

Having a lazy DBA shouldn't be a reason to jeopardize data integrity, fast or partial availability could be.