Data Integrity with MySQL 5.0

The IT crown jewels of any organization lies in their database infrastructure. Obviously, well-designed front end applications play a leading role when it comes to ensuring that critical information is captured or reviewed, but the buck stops at the database when it comes to data retention and protection.

Because such things are the database's responsibility, it is important that the underlying engine act in ways that preserve the integrity and validity of the data. This means the RDBMS should stand guard and deny entry of any and all invalid data through the welcome gates of the actual database.

For users of MySQL, this issue of server-enforced data integrity has been somewhat of a blessing and a curse. Traditionally, MySQL has taken the position that the front-end application should bear the responsibility of validating incoming data. There are countless users of MySQL who are completely fine with this approach, and many who actually prefer this form of data validation.

However, the meteoric rise in MySQL's popularity has brought many database "switch out" projects where MySQL is replacing proprietary or other open source database back ends where data integrity enforcement is expected at the database engine layer and not at the application layer. Most hard-core database professionals expect a database to validate incoming data and reject any data that is of the wrong datatype or data that violates certain data integrity rules.

The good news is that MySQL 5.0 now offers server-enforced data integrity. While the database server can still be used in its former mode of operation, database professionals wanting the RDBMS to be the ultimate authority with respect to accepting or rejecting data into/from the database can now easily make this happen in 5.0.

Read the complete article here.