Taking on Referential Integrity

Regardless of the medium you use to interact with the data, the database developer has one true goal-to protect the accuracy of the data. Getting the data to the web and vice versa takes a back seat while you're designing the database. In fact, you can completely forget that you're designing a database for the web and just treat your database as any other client/server project. Once the database is online, your job is to work with any constraints.

Note 

The relational model enforces referential integrity. For a database design to truly meet relational database rules, it must also enforce referential integrity. Most systems provide referential integrity features, but it's up to you to enforce them. Check your RDBMS's documentation on how to enable referential integrity and which options it supports.

Most RDMBSs provide a number of ways to protect data-from the simple elements such as appropriate datatypes to validation rules and even security. One method you might not be as familiar with is implementing referential integrity.

Integrity, in general, refers to a set of rules that maintain the relationships, and as a result, the data. There are three types of integrity rules:

Entity Each row must be uniquely identified. (This rule implies that a primary key value can't be Null.)

Referential A foreign key value must match a primary key value in a related table or be Null.

Business Enforce rules specific to the database, such as allowing each borrower to take only one book at a time.

All three of these rules protect your database from incorrect and missing data.

Referential integrity controls the data your users can modify, add, or delete by enforcing the relationships between tables. Specifically, this means the following:

• You can't allow users to enter a new foreign key value that doesn't match an existing primary key value in the related table. Such records are known as orphans, and they're not allowed in the relational model. Your RDMBS will probably allow you to enter orphans, so it's up to you to enforce referential integrity so that your users can't enter orphans.

• You can't allow users to delete an existing primary key value when it matches existing foreign key values in the related table. To do so would create orphans.

• You can't modify a primary key value if that value exists as a foreign key in a related table. You're not supposed to change a primary key value anyway. That's a great goal, but it might not be realistic, especially if your primary keys consist of natural data. If you do change a primary key value, you must also update all the matching foreign key values. Most RDBMSs can handle this task.

Note 

Within the context of this section, the terms primary key values and foreign key values refer to the entire record, not just the value.

You can't just apply referential integrity to any set of related tables. A few conditions must be met-and they all make sense when you consider the goal:

• The related fields must be a primary key or have a unique index.

• The related fields must be the same datatype.

• The related tables must be in the same database.

Note 

The above rules are generally accepted practice, but that doesn't mean your RDBMS enforces them. Check your documentation if you're not sure.

Most RDBMSs don't enforce referential integrity by default-you'll need to turn it on. Before doing so, your RDBMS will probably allow you to enter, modify, and delete any data at any time. Once you've turned it on, you can expect the following limitations:

• Your system will reject foreign key values if no matching primary key exists in the related table.

• Your system will reject your attempts to delete a primary key value when a foreign key value exists in a related table.

Note 

You can leave a foreign key field Null when entering a record, but referential integrity will be moot because your system won't be able to create the necessary join on the Null value.

Let's apply some referential integrity rules to our book database. Specifically, there's a one-to-many relationship between the books and publishers tables. Each book has one publisher, but each publisher can have many books listed in our database. After enforcing referential integrity between these two tables you can expect your system to do the following:

• Reject any record in which the publisher value doesn't match an existing publisher primary key value in the publisher table. If the publisher doesn't exist, you must enter the publisher before entering the book record. (You can't add a foreign key value before the primary key value exists.)

• Reject any attempt to delete a publisher from the publishers table if a record in the books table still refers to that publisher. To delete the publisher, you must first delete (or change) the matching book record. (You can't delete a primary key value when a foreign key value still exists.)

Initially, referential rules can be stymied by a lack of records and your system's compliance to the relational model. For instance, if your system doesn't allow Null values as foreign keys with referential integrity enabled, you'll have to make sure there are no orphan records before you can turn on the feature. Consult your system's documentation for more information on enabling referential integrity.