Constraints & Keys

Data that enters a database without rules is only as trustworthy as the application that inserted it — and applications have bugs, developers make mistakes, and direct database access bypasses application logic entirely. Constraints are the database's own defence: rules defined once at the schema level that apply to every INSERT, UPDATE, and DELETE regardless of which application, script, or developer is responsible for the change. A well-constrained schema makes bad data structurally impossible to store. This lesson covers every constraint type SQL Server supports, explains the real consequences each one prevents, and shows how to add, verify, and manage constraints on the DataplexaStore tables.

PRIMARY KEY Constraints

A primary key uniquely identifies every row in a table. It enforces two rules simultaneously: every value must be unique across all rows, and no value can be NULL. SQL Server automatically creates a clustered index on the primary key column by default, which means rows are physically stored on disk in primary key order. This makes lookups by primary key the fastest possible query — SQL Server navigates directly to the page containing the row without scanning anything. Every table should have a primary key. A table without one — called a heap — stores rows in no particular order, forces full scans for every lookup, and makes it impossible to efficiently identify and update a specific row.

-- View primary key details across all DataplexaStore tables
-- sys.indexes reveals the underlying index SQL Server built to enforce the PK

SELECT
    t.name                          AS table_name,
    i.name                          AS pk_index_name,
    i.type_desc                     AS index_type,   -- CLUSTERED by default
    c.name                          AS pk_column,
    ic.key_ordinal                  AS column_position
FROM sys.tables t
JOIN sys.indexes i
    ON t.object_id = i.object_id
   AND i.is_primary_key = 1         -- only the index backing the primary key
JOIN sys.index_columns ic
    ON i.object_id = ic.object_id
   AND i.index_id  = ic.index_id
JOIN sys.columns c
    ON ic.object_id = c.object_id
   AND ic.column_id = c.column_id
ORDER BY t.name;

-- Attempt to insert a duplicate primary key — must fail
INSERT INTO Products (ProductID, ProductName, Category, Price, StockQty)
VALUES (1, 'Duplicate', 'Test', 9.99, 1); -- ProductID 1 already exists

• All five primary keys are CLUSTERED — this means physical row order on disk matches primary key order, making range scans and single-row lookups by primary key the most efficient possible access pattern
• Msg 2627 is the primary key violation error — it names the constraint and shows the duplicate value, making it immediately clear what the problem is
• Because ProductID is an IDENTITY column, you would never normally supply it in an INSERT — this example uses an explicit value only to demonstrate the violation; in practice the IDENTITY mechanism prevents this entirely

FOREIGN KEY Constraints

A foreign key constraint ensures that a value in a child table's column always has a matching value in the referenced parent table's column. It enforces referential integrity — the guarantee that relationships between tables are always valid and navigable. Without foreign keys, orphaned rows accumulate silently: orders for users who were deleted, order items pointing to products that no longer exist. SQL Server checks the foreign key on every INSERT and UPDATE to the child table, and on every DELETE and UPDATE to the parent table. The ON DELETE and ON UPDATE clauses define what SQL Server does when a parent row is affected — NO ACTION raises an error and blocks the operation, CASCADE propagates the change automatically, SET NULL clears the foreign key column in child rows, and SET DEFAULT replaces it with the column's default value.

-- View all foreign key relationships and their referential action settings

SELECT
    fk.name                         AS fk_constraint_name,
    OBJECT_NAME(fk.parent_object_id)    AS child_table,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id)
                                    AS fk_column,
    OBJECT_NAME(fk.referenced_object_id) AS parent_table,
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)
                                    AS referenced_column,
    fk.delete_referential_action_desc AS on_delete,
    fk.update_referential_action_desc AS on_update
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id
ORDER BY child_table;

-- Attempt to insert an order for a user that does not exist — must fail
INSERT INTO Orders (UserID, TotalAmount)
VALUES (9999, 59.99);               -- UserID 9999 does not exist in Users

• NO_ACTION on all constraints is the safest default for business data — it forces the application to manage the deletion order explicitly, preventing accidental data loss from a careless DELETE on a parent table
• CASCADE delete is appropriate only when child rows are genuinely meaningless without their parent — for example, soft-delete audit log entries that have no business value if their parent record is removed
• Temporarily disabling a foreign key with ALTER TABLE tablename NOCHECK CONSTRAINT fk_name is sometimes used during bulk data loads — always re-enable and validate with WITH CHECK CHECK CONSTRAINT afterwards to confirm integrity was not violated during the load

UNIQUE Constraints

A UNIQUE constraint ensures no two rows in the table share the same value in the constrained column or combination of columns. It differs from a primary key in two ways: a table can have multiple UNIQUE constraints, and UNIQUE columns allow NULL values (SQL Server treats each NULL as distinct from every other NULL, so multiple NULLs are permitted). SQL Server enforces UNIQUE constraints by creating a unique nonclustered index behind the scenes, which also makes the column faster to search. In DataplexaStore the Email column on the Users table has a UNIQUE constraint — it is the business rule that no two customer accounts can share an email address, enforced at the database level so it cannot be bypassed by any application that connects to this database.

-- Inspect all UNIQUE constraints and the indexes backing them

SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    kcu.COLUMN_NAME,
    i.type_desc                     AS backing_index_type
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
JOIN sys.indexes i
    ON OBJECT_ID(tc.TABLE_NAME) = i.object_id
   AND i.name = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'UNIQUE';

-- Attempt to insert a duplicate email — must fail
INSERT INTO Users (FullName, Email, City, Country)
VALUES ('Test User', 'alice.johnson@example.com', 'London', 'UK');
-- alice.johnson@example.com already exists in the Users table

• The backing index is NONCLUSTERED because the clustered index is already taken by the primary key — only one clustered index per table is allowed
• Msg 2627 is the same error number for both primary key and unique constraint violations — the constraint name in the message distinguishes which rule was broken
• A composite UNIQUE constraint spanning two columns — for example ensuring a user can only review each product once — would be written as CONSTRAINT UQ_Reviews_UserProduct UNIQUE (UserID, ProductID) at the table level

CHECK Constraints

A CHECK constraint defines a boolean expression that every row must satisfy on INSERT and UPDATE. If the expression evaluates to false for a row, SQL Server rejects the operation with error Msg 547. If it evaluates to true or unknown (which happens when NULL is involved), the row is accepted. CHECK constraints are the right tool for business rules that apply to a single row — valid ranges, allowed values, conditional requirements. They are not the right tool for cross-row rules (ensuring a total matches a sum of line items) or cross-table rules (ensuring an order date is after the customer's join date) — those require triggers or application logic.

-- View all CHECK constraints and their expressions

SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    cc.CHECK_CLAUSE                 -- the boolean expression that every row must satisfy
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
    ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
ORDER BY tc.TABLE_NAME;

-- Add a new CHECK constraint to limit MembershipTier to known values
ALTER TABLE Users
    ADD CONSTRAINT CK_Users_MembershipTier
    CHECK (MembershipTier IN ('Standard', 'Premium', 'VIP'));

-- Test it — this should fail because 'Gold' is not an allowed tier
INSERT INTO Users (FullName, Email, City, Country, MembershipTier)
VALUES ('Test User', 'test@example.com', 'Paris', 'France', 'Gold');

• SQL Server rewrites IN (...) expressions as a series of OR comparisons in the stored CHECK_CLAUSE — both forms are semantically identical and equally enforced
• Adding a CHECK constraint to a table that already has data triggers a validation scan of all existing rows — if any row fails the expression, the ALTER TABLE is rejected and the constraint is not created; add WITH NOCHECK to skip validation of existing rows (though this leaves the constraint in an untrusted state)
• CHECK constraints only run on INSERT and UPDATE — they do not retroactively fix existing data, which is why adding them to tables with existing data requires careful validation first

DEFAULT Constraints

A DEFAULT constraint provides a fallback value for a column when an INSERT statement does not supply one. When SQL Server processes an INSERT that omits a column covered by a DEFAULT, it substitutes the default expression automatically — no application code required. Defaults can be literal values, system functions like GETDATE() or NEWID(), or any deterministic expression. They are defined either inline in CREATE TABLE or separately with ALTER TABLE ADD CONSTRAINT. A DEFAULT is not a validation rule — it does not prevent an application from explicitly supplying a different value. Its purpose is convenience and safety: ensuring that rows never accidentally receive NULL in a column that has a sensible universal starting value.

-- View all DEFAULT constraints across the DataplexaStore schema

SELECT
    t.name                          AS table_name,
    c.name                          AS column_name,
    dc.name                         AS constraint_name,
    dc.definition                   AS default_expression
FROM sys.default_constraints dc
JOIN sys.columns c
    ON dc.parent_object_id = c.object_id
   AND dc.parent_column_id = c.column_id
JOIN sys.tables t
    ON c.object_id = t.object_id
ORDER BY t.name, c.name;

-- Insert a user without supplying MembershipTier or JoinDate
-- Both should be filled by their DEFAULT constraints automatically
INSERT INTO Users (FullName, Email, City, Country)
VALUES ('Priya Sharma', 'priya.sharma@example.com', 'Mumbai', 'India');

SELECT UserID, FullName, MembershipTier, JoinDate
FROM Users
WHERE Email = 'priya.sharma@example.com';

• MembershipTier received 'Standard' and JoinDate received today's date — both supplied automatically by their DEFAULT constraints without any application code providing those values
• DEFAULT constraints are evaluated at INSERT time — GETDATE() captures the current timestamp at the moment each row is inserted, not when the table was created or the constraint was defined
• Supplying an explicit value in the INSERT column list always overrides the DEFAULT — the DEFAULT only fires when the column is absent from the INSERT entirely

Disabling and Trusting Constraints

There are legitimate scenarios where constraints need to be temporarily bypassed — bulk data loads from a trusted external system, data migrations, or seeding a database with historical data that was validated elsewhere. SQL Server allows foreign key and CHECK constraints to be disabled with ALTER TABLE NOCHECK CONSTRAINT, which suspends enforcement without dropping the constraint. After the load completes, re-enabling and validating the constraint with WITH CHECK CHECK CONSTRAINT confirms that all data — including what was loaded while the constraint was disabled — satisfies the rule. A constraint re-enabled with just CHECK CONSTRAINT (without WITH CHECK) is marked as untrusted by SQL Server and the query optimiser will not use it to eliminate unnecessary joins or filter operations in execution plans, which is a subtle but real performance penalty.

-- Check which constraints are currently trusted vs untrusted
-- is_not_trusted = 1 means the optimiser cannot rely on this constraint

SELECT
    OBJECT_NAME(parent_object_id)   AS table_name,
    name                            AS constraint_name,
    type_desc                       AS constraint_type,
    is_disabled,                    -- 1 = currently disabled
    is_not_trusted                  -- 1 = optimiser cannot rely on it
FROM sys.check_constraints
UNION ALL
SELECT
    OBJECT_NAME(parent_object_id),
    name,
    type_desc,
    is_disabled,
    is_not_trusted
FROM sys.foreign_keys
ORDER BY table_name, constraint_name;

-- Correct way to re-enable a constraint after a bulk load
-- WITH CHECK forces validation of ALL existing rows including those loaded while disabled
ALTER TABLE Orders
    WITH CHECK CHECK CONSTRAINT FK_Orders_Users;

• is_disabled = 0 and is_not_trusted = 0 across all constraints confirms the DataplexaStore schema is in a healthy, fully enforced state
• A constraint with is_not_trusted = 1 still enforces rules on new INSERT and UPDATE operations — it is only untrusted for the query optimiser, which cannot use it to skip join steps or filter rows early in the execution plan
• Always use WITH CHECK CHECK CONSTRAINT (not just CHECK CONSTRAINT) when re-enabling after a bulk load — the extra WITH CHECK forces a full validation scan that sets is_not_trusted back to 0

Summary Table

Practice Questions

Quiz

Next up - Identity Columns - Understand how SQL Server auto-generates primary key values and controls the sequence behind every IDENTITY column.