SQL Server Data Integrity Elements

Because the sole purpose of SQL Server is to handle data, data integrity, the accuracy and consistency of that data across its entire lifecycle, is one of the most important things a database enforces. Data integrity problems occur when tables aren't linked together correctly, such as when a value in one table refers to a value in another table that has since been deleted. Left unaddressed, a breakdown like this cascades into inconsistent queries, unreliable reports, and application errors.

SQL Server 2025 maintains integrity through a short list of mechanisms you'll use constantly: primary and foreign keys, which guarantee each row is unique and that relationships between tables stay valid; constraints such as NOT NULL, UNIQUE, and CHECK, which enforce rules at the column level; cascading actions, which decide what happens to child rows when a parent row they depend on is deleted or changed; and transactions, which ensure a set of related changes either all succeed together or none of them do. The rest of this lesson organizes these mechanisms, and SQL Server 2025's newer additions to them, around the four classical pillars of relational integrity.

SQL Server organizes data integrity into four classical categories, each guaranteeing a different kind of correctness. Enforcing data integrity well means combining all four, rather than relying on any single one.

Integrity Type	What It Guarantees
Entity integrity	Every row in a table is a unique, identifiable instance; no duplicate records.
Domain integrity	Every column's values are restricted to a valid type, format, range, and nullability.
Referential integrity	Relationships between parent and child tables stay valid; no orphaned rows.
User-defined integrity	Business rules that span multiple rows or tables, beyond what keys and domains alone can express.

Entity integrity means every row in a table can be told apart from every other row. SQL Server enforces it through three mechanisms, used alone or in combination:

Mechanism	Description
Primary key	A combination of one or more columns that uniquely identifies a row in a table. Automatically NOT NULL.
Unique constraint	One or more columns that cannot contain duplicate values across the rows of the table. SQL Server enforces it by automatically creating a unique index.
Identity property	An attribute of a column that automatically generates a unique value, typically an incrementing integer, for each new row, without the application having to supply one.

A fourth option, a SEQUENCE object, generates unique values independently of any one table, which is useful when several tables, or an application layer, need to draw from the same ordered series of numbers rather than each table generating its own.

Entity integrity has to hold even under heavy concurrent write activity, and SQL Server 2025's Optimized Locking helps here directly: rather than holding a large number of individual row or page locks in memory to check uniqueness during a bulk insert, the engine tracks a single Transaction ID (TID) per transaction and releases individual row locks as soon as each row is validated, keeping uniqueness checks correct without the lock-memory overhead that used to risk escalating to a full table lock.

Domain integrity restricts the values a column may legally hold: not just its data type, but its format, its range, and whether it can be NULL. It's enforced through the column's data type itself, NOT NULL and DEFAULT definitions, and CHECK constraints, a user-defined rule dictating what counts as a valid value for that column. A CHECK constraint on an OrderDate column, for instance, could reject any date later than today, if a business rule says orders can't be placed for the future.

SQL Server 2025 extends domain integrity well past the type system you may already be used to:

• A native JSON column parses and validates well-formed JSON syntax on every INSERT or UPDATE, storing it in an optimized binary format rather than as unvalidated text in an NVARCHAR column.
• A native VECTOR(n) column strictly enforces its declared dimension count; VECTOR(768) rejects any embedding that isn't exactly 768 single-precision floating-point values.
• A CHECK constraint can call the native REGEXP_LIKE function to enforce complex patterns, an email address, an IBAN, a product SKU, directly at the column level, rather than relying on looser LIKE wildcards. REGEXP_LIKE takes an optional third flags argument; passing 'i' makes the match case-insensitive, since matching is case-sensitive by default. The function requires database compatibility level 170 or higher.

A single CREATE TABLE statement shows several of these mechanisms working together:

CREATE TABLE Sales.Customers (
    CustomerID INT IDENTITY(1,1)
        CONSTRAINT PK_Customers PRIMARY KEY,                   -- Entity integrity
    AccountNumber VARCHAR(12) NOT NULL
        CONSTRAINT UQ_Customers_Account UNIQUE,                -- Entity integrity (alternate key)
    SalesRepID INT NULL
        CONSTRAINT FK_Customers_SalesRep                       -- Referential integrity
        FOREIGN KEY REFERENCES HumanResources.Employees(EmployeeID)
        ON DELETE SET NULL,
    Email VARCHAR(255) NOT NULL
        CONSTRAINT CK_Customers_Email_Valid                    -- Domain integrity (regex)
        CHECK (REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$', 'i')),
    Preferences JSON NOT NULL                                  -- Domain integrity (native JSON)
        CONSTRAINT DF_Customers_Preferences DEFAULT ('{"notifications": true}'),
    ProfileEmbedding VECTOR(768) NULL                          -- Domain integrity (vector dimension)
);

Once entity and domain integrity hold within a table, referential integrity, sometimes called relational integrity, guarantees the relationships between tables stay valid. It's enforced through foreign keys: one or more columns in a child table that must match a primary key or unique key value in a parent table. The FOREIGN KEY example above shows this directly: SalesRepID must correspond to a real EmployeeID, or be NULL. The same idea holds for a simpler, more familiar pairing: if a foreign key in an Orders table references a Customers table, SQL Server enforces that the referenced customer actually exists before it allows the order to be inserted.

A foreign key also defines what happens when a referenced parent row is deleted or its key changes, through a cascading action: CASCADE propagates the delete or update to matching child rows, SET NULL or SET DEFAULT replace the orphaned value instead, and NO ACTION, the default, simply blocks the operation. If a customer is deleted, for example, you could configure the foreign key on Orders to cascade the delete, or to set the customer reference to NULL instead, rather than leaving orphaned order records behind.

Under Read Committed Snapshot Isolation, SQL Server 2025's Lock After Qualification evaluates a statement's filter predicates against the latest committed row version before acquiring any lock, so validating a foreign key relationship during a large update blocks less concurrent activity than it used to.

Some rules can't be expressed as a key, a data type, or a simple CHECK constraint, they depend on business logic specific to the organization. User-defined integrity covers these cases, enforced procedurally through DML triggers (AFTER, INSTEAD OF), stored procedures, and user-defined functions. A later lesson in this course covers rules, triggers, and stored procedures in depth.

SQL Server 2025 adds a useful tool to this category: native fuzzy string-matching functions, EDIT_DISTANCE and JARO_WINKLER_SIMILARITY, which measure how similar two strings are rather than whether they match exactly. Called from inside a trigger or stored procedure, they let you catch likely near-duplicate entries, two customer names differing only by a typo, say, that an exact-match CHECK constraint would never flag.

A constraint that SQL Server hasn't verified against a table's existing data is marked untrusted, and the Query Optimizer won't rely on it when building execution plans, which can mean slower queries even though the constraint is technically still enforced for new data. This typically happens when a FOREIGN KEY or CHECK constraint is disabled for a bulk load (ALTER TABLE ... NOCHECK CONSTRAINT) and then re-enabled without revalidating prior rows. SQL Server marks it is_not_trusted = 1 in sys.foreign_keys or sys.check_constraints. Running ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ALL revalidates every existing row and restores trusted status, letting the optimizer use the constraint again.

Beyond validating data as it's written, SQL Server 2025 can protect data against unauthorized changes after the fact, and preserve its history:

• Ledger tables (updatable or append-only) protect data integrity even against privileged insiders, including DBAs. SQL Server computes SHA-256 hashes of modified rows into a Merkle tree structure and generates digests automatically, which can be offloaded to immutable storage such as Azure Confidential Ledger or WORM storage. Running sys.sp_verify_database_ledger mathematically proves whether any underlying data was tampered with outside normal T-SQL operations.
• System-versioned temporal tables automatically record the exact ValidFrom/ValidTo timestamps of every row change into a locked-down history table, preserving a verifiable audit trail that application code can't bypass.

Even a perfectly designed schema fails if a concurrent operation leaves a table in a half-written state, or a hardware fault silently corrupts a data page. SQL Server enforces integrity at this lower level through several mechanisms:

• Transactions. Wrapping a set of related changes, say, updating both an Orders row and the corresponding Inventory row, in a single transaction ensures they either all commit together or are all rolled back together, so a failure partway through never leaves the database in an inconsistent in-between state.
• Write-ahead logging and Accelerated Database Recovery (ADR). Every modification is written to the transaction log before its data page is hardened to disk. ADR, which applies to both user databases and tempdb in SQL Server 2025, maintains a Persisted Version Store so a rolled-back transaction reverts instantaneously rather than leaving a partial change behind.
• Page-level checksums. When an 8 KB page is written to disk, SQL Server calculates a checksum across the page and stores it in the page header; reading the page back and recalculating a mismatched checksum immediately raises Error 824, catching silent disk corruption before it can reach query results.
• Database consistency checks. DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKCONSTRAINTS verify the physical and logical integrity of index pointers, allocation structures, and row-level links across the database.

Entity, domain, referential, and user-defined integrity, backed by these cryptographic and physical safeguards, together keep a database trustworthy from the moment data is written to the moment it's read back years later.

In the next lesson, you'll learn about business rules and how they shape your data model.