Constraints and Keys

A PRIMARY KEY constraint guarantees that every value (or combination of values, for a composite key) in the constrained column(s) is both unique and non-null, and SQL Server automatically creates a unique index to enforce it — clustered by default unless you explicitly specify NONCLUSTERED. A UNIQUE constraint is similar but allows a single NULL (or, for composite unique constraints, allows combinations involving NULL since NULL is never considered equal to another NULL), and a table can have many UNIQUE constraints but only one PRIMARY KEY, making the primary key the canonical, non-nullable identity of each row.

A FOREIGN KEY constraint ties a column in one table to a PRIMARY KEY or UNIQUE constraint in another (or the same, for self-referencing hierarchies like an employee's manager), and SQL Server rejects any INSERT or UPDATE that would create a value with no matching parent row, and by default rejects deleting a parent row that still has child rows referencing it. Optional ON DELETE CASCADE or ON UPDATE CASCADE clauses let you automate propagating a parent delete or key change to matching child rows instead of blocking the operation, though cascades must be used carefully since a single DELETE can ripple through many related tables at once.

A CHECK constraint evaluates a Boolean expression against each row and rejects any INSERT or UPDATE that would make it false — for example CHECK (Salary >= 0) or a more complex cross-column rule like CHECK (EndDate > StartDate) — enforcing business rules at the data layer so they hold true regardless of which application or ad hoc script writes to the table. A DEFAULT constraint supplies a value automatically when a column is omitted from an INSERT, and NOT NULL simply forbids the column from ever holding NULL; together with CHECK, these are the cheapest, most reliable way to guarantee data quality because, unlike application-level validation, they cannot be bypassed by a bug in one particular codepath.