Database Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table and automatically creates a unique index. It enforces both entity integrity (no duplicate rows) and non-nullability.

• Example: CREATE TABLE users (user_id INT PRIMARY KEY, email VARCHAR(255));
• Use Case: Guaranteeing that every customer, product, or transaction has a unique, non-null identifier. Attempting to insert a duplicate user_id or a NULL value will be rejected by the DBMS.

A FOREIGN KEY constraint enforces referential integrity by ensuring a value in one table matches a primary key in another. It prevents orphaned records and maintains logical links.

• Example: CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id));
• Use Case: Linking an order to an existing customer. The DBMS will block any INSERT or UPDATE that references a non-existent customer_id. CASCADE actions can automate updates or deletions.

The CHECK constraint validates that a column's value meets a specified Boolean condition, embedding business rules directly into the database schema.

• Example: CREATE TABLE products (price DECIMAL(10,2) CHECK (price > 0), stock_quantity INT CHECK (stock_quantity >= 0));
• Use Case: Ensuring data adheres to domain logic, such as preventing negative prices or inventory counts. It acts as a first-line defense against invalid data entry before application code processes it.

The UNIQUE constraint ensures all values in a column (or set of columns) are distinct from one another, but unlike a primary key, it allows NULL values (typically one NULL per column, depending on the DBMS).

• Example: CREATE TABLE employees (employee_id INT PRIMARY KEY, national_id VARCHAR(20) UNIQUE);
• Use Case: Preventing duplicate entries for fields that must be distinct but are not the primary identifier, such as email addresses, social security numbers, or SKU codes.

The NOT NULL constraint is a fundamental rule that prevents a column from containing a NULL (unknown or missing) value, enforcing data completeness at the column level.

• Example: CREATE TABLE invoices (invoice_number VARCHAR(50) NOT NULL, issue_date DATE NOT NULL);
• Use Case: Mandating that critical fields like a username, order_date, or product_name must always be provided. It simplifies application logic by guaranteeing the presence of required data.

When a modern ETL/ELT pipeline loads data into a constrained database table, any violation will cause the entire transaction to fail unless handled. This makes constraints a critical component of data quality posture.

• Real Impact: A FOREIGN KEY violation might indicate a missing dimension record in a data warehouse load, halting the nightly refresh.
• Observability Link: Failed constraints generate specific error codes (e.g., PostgreSQL's 23503 for foreign key violations), which should be captured by pipeline monitoring systems to trigger alerts and data incident management workflows.

Schema validation is the process of verifying that a data structure conforms to a predefined formal specification, or schema, which defines the expected format, data types, and structural constraints. While database constraints are enforced at the database engine level, schema validation is often a broader concept applied during data ingestion or in application code.

• Key Distinction: A constraint is a rule inside the database; schema validation can occur before data reaches the database.
• Common Tools: This is implemented using standards like JSON Schema, Avro Schema, or XML Schema (XSD).
• Use Case: Validating an API payload against an OpenAPI specification before it's processed is an example of schema validation.

A data contract is a formal agreement between data producers and consumers that specifies the schema, semantics, quality guarantees, and service-level expectations for a data product. It operationalizes integrity rules beyond the database.

• Beyond Constraints: While a PRIMARY KEY is a technical constraint, a data contract is a business and technical SLA that may include constraints as one component.
• Enforcement Layer: Contracts often rely on schema validation and data quality rules to ensure compliance.
• Evolution: They govern schema evolution, ensuring changes are communicated and managed without breaking downstream systems.

Data integrity refers to the overall accuracy, consistency, and reliability of data throughout its lifecycle. Database constraints are a primary technical mechanism for enforcing it.

• Holistic Concept: Integrity encompasses entity integrity (enforced by PRIMARY KEY), referential integrity (enforced by FOREIGN KEY), and domain integrity (enforced by CHECK and data type constraints).
• Supporting Processes: It is also maintained through data cleansing, normalization, and robust data lineage tracking.
• Broader Than DB: Integrity concerns also apply to data in motion, managed via ETL validation and data quality rules.

Referential integrity is a specific type of data integrity enforced by the FOREIGN KEY constraint in relational databases. It ensures that relationships between tables remain consistent.

• Core Rule: Any foreign key value in a child table must reference an existing primary key value in the parent table, or be NULL if allowed.
• Cascade Actions: DBMSs can enforce actions like ON DELETE CASCADE to automatically maintain integrity when referenced rows are removed.
• Prevents Orphans: This constraint prevents "dangling references" or orphaned records that point to non-existent data.

Schema evolution is the practice of managing changes to a data schema over time while maintaining compatibility with existing data and applications. It directly interacts with how constraints are modified.

• Compatibility Modes: Governed by rules like backward compatibility (old code reads new data) and forward compatibility (new code reads old data).
• Tooling: Managed centrally in streaming architectures using a schema registry.
• Risk: Poorly managed evolution leads to schema drift, where unplanned changes break pipelines. Adding a NOT NULL constraint to an existing column is a high-risk evolutionary change.

A data quality rule is a formal, testable assertion that defines a constraint or condition data must satisfy to be considered fit for use. Database constraints are a subset of executable data quality rules.

• Scope: Rules can be applied at the database level (CHECK constraint), application level, or in a data observability platform.
• Examples: Include completeness checks (non-nullable), regex validation for format, duplicate detection (uniqueness), and business logic like age > 18.
• Automation: These rules form the basis of automated data testing and are measured via data quality metrics.