4.6: Data Integrity and Constraints

Data Integrity and Constraints

In relational databases, integrity is often enforced by a system of constraints – rules that the data must satisfy. We have already touched on some (like primary keys for uniqueness and not-null for completeness). Two other crucial types of integrity constraints are referential integrity and entity integrity, as well as general check constraints. Enforcing these constraints early (at data entry or ingestion) is one of the best practices for preventing bad data from contaminating the dataset.

Referential integrity (Foreign Keys): Referential integrity ensures that relationships between tables remain consistent. The typical mechanism is a foreign key constraint on a child table that references a primary key in a parent table. For example, if we have an Orders table with a CustomerID field, that CustomerID should correspond to an existing record in the Customers table. A foreign key constraint on Orders (referencing Customers) would prevent entering an Order with a non-existent CustomerID. In a clean dataset, every reference should resolve – there should be no “orphan” records. If referential integrity is broken, you might have, say, an order that points to a customer that isn’t in the customer table (perhaps the customer entry got deleted or never added).

Figure \(\PageIndex{1}\): Depicts how entity integrity (primary keys), referential integrity (foreign keys), and domain integrity (check constraints) work together to maintain data quality across related tables. (CC BY 4.0; Felix Amoruwa) Access to a detail description of Figure \(\PageIndex{1}\)

During data cleaning, it's important to check for such issues if the data model expects those relations. A simple check is to do an anti-join: e.g., SELECT o.OrderID FROM Orders o LEFT JOIN Customers c ON o.CustomerID = c.CustomerID WHERE c.CustomerID IS NULL;. This finds orders with no matching customer. If any appear, we have invalid data. Possible remedies include: if the missing parent data can be retrieved or re-created, do so; otherwise, those child records are problematic (they might be removed, or assigned to a placeholder customer like "Unknown Customer" if analysis requires keeping them).

In practice, foreign key constraints are sometimes not enforced in certain data warehouses or during initial data loads, often as a convenience to allow loading data out-of-order or with temporary inconsistencies. However, this means the onus is on the data cleaning process to identify and correct any referential mismatches. A data warehouse might accept an order with a non-existent customer because it turned off FK constraints for speed – but an analyst should treat that as dirty data to be addressed. It’s generally better to enforce these constraints where possible, as it prevents accumulating hidden problems that “will be fixed later” (a “later” that often never comes).

Entity integrity:

We already discussed entity integrity in terms of primary keys (unique and not null). Beyond primary keys, entity integrity is about each record being meaningful and valid in itself. This means all required fields are present (not null), and any computed or dependent fields are consistent. For example, if there’s a TotalPrice field in an order that is supposed to equal quantity * unit_price, each record should follow that. That could be seen as a business rule, but also part of the integrity of each record.

Check constraints:

SQL allows general check constraints on columns or tables. These are custom rules like CHECK (quantity > 0) on an OrderItems table (preventing non-positive quantities), or CHECK (LOWER(state_code) IN ('ca','ny','tx', ...)) to ensure a state code is one of a predefined list. During data cleaning, if the data source did not have such checks, you effectively apply them manually. A useful approach is to try to add the constraints to a test copy of the table and see which rows violate them, or simply run equivalent queries. Check constraints embody many of the business rules and range validations we discussed, but as formally defined rules. They help maintain domain integrity – that values in a column belong to the defined domain (set of acceptable values).

Integrity across time:

Another aspect of data integrity is consistency across the dataset over time. For instance, if a database keeps historical snapshots, one might check that an employee’s status transitions follow allowed patterns (they can’t go from “terminated” back to “active” without a rehire event, etc.). These temporal integrity rules can be complex, but for static datasets, they might not apply.

Why enforce constraints early:

The mantra “enforce constraints early” means that the earlier in the data pipeline you apply these rules, the less bad data you have to clean later. If you can enforce at data entry (application level or database level), do it. If not, enforce as soon as data lands in your system (e.g., when loading into a staging database, apply constraints or at least run validation queries). This prevents bad data from propagating downstream. Early enforcement reduces the cost of cleaning and the risk of bad data-driven decisions. It’s like quality control in manufacturing – catching a defect at the station where it occurs is much cheaper than recalling a finished product.

Example: Suppose we have a Sales database with SalespersonID on the Sales table referencing an Employee table. If someone enters a sale with a SalespersonID that doesn’t exist (say a typo in the ID), and if no foreign key is enforced, that sale record will sit there but essentially be “hanging”. If not caught, analysis of sales by salesperson will either drop that record (if doing an inner join) or include it under a null/unknown salesperson (if doing a left join), possibly skewing results. If we had a foreign key, that record would have been rejected at insertion, forcing a correction (the ID fixed or the sale assigned to a valid rep) immediately. Without it, our cleaning must find it. The cleaning solution might be to create a dummy Employee entry like “Unknown Salesperson” with that ID just to maintain referential integrity, or to fix the ID if we can guess the intended salesperson, or to exclude that sale from analysis. None of those is as ideal as having prevented it.

Data integrity tools:

In data warehousing, there are frameworks and tools for data quality checks (for instance, dbt tests, Great Expectations, or custom scripts) that automate checking integrity rules. They can be scheduled to run whenever new data arrives, providing a report on any violations so they can be fixed promptly. For an analyst working ad-hoc, it might be more manual: writing a series of SELECT queries to test each assumption.

In summary, maintaining data integrity means ensuring that all pieces of the data are consistent with each other and with reality. Primary and foreign keys, unique and not-null constraints, and check rules are all formal ways to encode integrity expectations. As part of data preparation, one should validate that these conditions hold, and if not, decide how to handle the discrepancies (correct, exclude, or flag). High-quality datasets conform to strict integrity rules[20], which is why an academic or industry-grade analysis will always include this step of verifying and cleaning for integrity.