Crosstab Software and the Importance of Referential Integrity

Crosstab software is hailed for showcasing the relationship between various data points. But it’s not going to be able to do its job correctly if you don’t pay attention to maintaining relationships throughout your database. That’s where referential integrity comes in.Referential integrity is when more than one table shares a relationship based on data stored in the tables. Enforcing referential integrity means ensuring that the relationship remains consistent throughout all the tables sharing that data.ExamplesChecking out an example is probably the best way to delve deeper into the concept. Let’s say your company has two different tables, one for employees and one for employee salaries.EmployeesEmployee ID Employee Name100John Doe101Jane Deer102Henry FawnEmployee SalariesEmployee ID Salary for ID100$40K101$50K102$60KNow let’s say Jane Deer left the company. Since she now longer works with you, you need to remove her from the employee table. This removes both her name and her employee ID from use. Because her employee ID also exists in the employee salaries table, however, you would need to manually remove the data from that table as well.Thus removing Jane Deer from the database would involve manually removing her information from at least the employees and employee salaries tables. If her employee ID has also been used in other tables, her data would again need to be manually removed. This would apply to every single table in which her employee ID appears.Depending on the type of employee information you stored in tables, removing even a single employee from each table could turn into a very tedious and incredibly time consuming practice.EmployeesEmployee ID Employee Name100John Doe101Jane Deer102Henry FawnEmployee SalariesEmployee ID Salary for ID100$40K101$50K102$60KEnforcing referential integrity can come to the rescue, allowing you to avoid the huge hassle of manually removing Jane Deer Employee 101 from each and every table in which the data is included.How to Enforce Referential Integrity

• Choose a primary key. In the case above, the employee ID column in the employee table would serve as the primary key.
• Designate foreign keys. Once you’ve defined a primary key in a main table, define the same information as a foreign key that points to the primary key in all other tables.

EmployeesEmployee ID - PRIMARY KEYEmployee Name100John Doe101Jane Deer102Henry FawnEmployee SalariesEmployee ID – FOREIGN KEY that points to PRIMARY KEY in Employee tableSalary for ID100$40K101$50K102$60K

• Add a constraint, in this case a “cascading delete.” Once you’ve established the foreign-to-primary key relationship, you then add what is known as a “constraint” to the tables containing the foreign keys. The constraint in this case would be a “cascading delete.” A cascading delete ensures any foreign keys that are linked to a primary key are removed when the primary key is removed.

If the cascading delete restraint were set up in your employees and employee salaries tables, the act of deleting Jane Deer Employee 101 in the employees table would automatically result in her deletion from the employee salaries table.Three Rules Being EnforcedEnforcing referential integrity typically consists of enforcing three different rules. They involve deleting existing records (as outlined above), adding new records, and updating existing records.New records can only be added to tables with foreign keys if a record containing the primary key already exists. Changes to the primary key can be updated throughout tables containing foreign keys with a “cascading update” constraint.Enforcing referential integrity eliminates the tedious manual process and the chance of human error. It also helps ensure your crosstab software and other functions that rely on database information remain accurate and error-free.