Thursday, November 25, 2010

Referential Integrity - Good or Bad?

In relational database world such as MySQL, PostgreSQL, Derby / JavaDB, and HSQLDB RDBMS there is Referential Integrity.

It's very useful to avoid consistency mistakes with foreign keys during operation.

It's useful when we live in *relational* world. But during development of a modular application and agile, frequent upgrades.. Is referential integrity helping or hindering productivity?

Consider an application that has a Customer table that has a column an refers to a Country table. Each deployment would have its own Customer table data. However, Country table is pretty much "shared" globally. Country table is never meant to be modified from the admin's perspective.

When there is a new country or modification, new version of application will be released, containing an update to Country table.

In old-school style of upgrades, Country table should be replaceable similar to how we replace files during upgrade, i.e. overwriting a file called countries.xml.

However, due to referential integrity, it's not possible to simply drop the table, recreate it with the data. We have to issue proper DML SQL statements to update the data from the "current" version (yes, we must detect what is the current version) to the new version.

All in the name of not breaking foreign key checks aka referential integrity.

Isn't RDBMS making simple things complex?