Primary Keys and Uniqueness
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.
Cricket analogy: A PRIMARY KEY is like each player's unique international cap number — no two players ever share one, and every capped player has one, unlike a UNIQUE jersey number constraint which could theoretically be left unassigned for an uncapped squad member.
CREATE TABLE dbo.Employees (
EmployeeId INT IDENTITY(1,1) NOT NULL,
Email VARCHAR(255) NOT NULL,
ManagerId INT NULL,
Salary DECIMAL(10,2) NOT NULL DEFAULT (0),
HireDate DATE NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED (EmployeeId),
CONSTRAINT UQ_Employees_Email UNIQUE (Email),
CONSTRAINT CK_Employees_Salary CHECK (Salary >= 0),
CONSTRAINT FK_Employees_Manager FOREIGN KEY (ManagerId)
REFERENCES dbo.Employees (EmployeeId)
);Foreign Keys and Referential Integrity
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.
Cricket analogy: A FOREIGN KEY is like every ball bowled in a match record referencing a valid, already-registered bowler ID — you can't log a delivery from a bowler who was never entered in the playing XI.
CHECK, DEFAULT, and NOT NULL
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.
Cricket analogy: A CHECK constraint enforcing Overs <= 50 in an ODI innings record is like the umpire refusing to allow a 51st over to be bowled — the rule is enforced at the point of play, not left to the scorer to remember.
Constraints are validated inline as part of every write, so they are effectively free insurance against bad data compared to catching the same bug later through application-level bugs in reports or downstream ETL jobs. Naming constraints explicitly (CONSTRAINT CK_Employees_Salary CHECK ...) instead of letting SQL Server auto-generate a name also makes them much easier to find and drop later.
Adding a FOREIGN KEY or CHECK constraint to a table with existing data will fail if any existing row violates it; use WITH NOCHECK to add the constraint without validating existing rows, but be aware the constraint is then marked 'not trusted' and the optimizer won't rely on it for query plan decisions until you run CHECK CONSTRAINT WITH CHECK to revalidate.
- PRIMARY KEY enforces uniqueness and non-nullability, with SQL Server auto-creating a clustered unique index unless NONCLUSTERED is specified.
- UNIQUE constraints allow at most one NULL and a table can have multiple UNIQUE constraints but only one PRIMARY KEY.
- FOREIGN KEY enforces that a column's value must match an existing PRIMARY KEY/UNIQUE value in the referenced table.
- ON DELETE CASCADE / ON UPDATE CASCADE automate propagating parent changes to child rows instead of blocking the operation.
- CHECK constraints enforce arbitrary Boolean business rules at the row level, independent of any application code.
- DEFAULT supplies a value when a column is omitted from an INSERT; NOT NULL simply forbids NULL values.
- WITH NOCHECK adds a constraint without validating existing data, marking it untrusted until explicitly revalidated.
Practice what you learned
1. How many PRIMARY KEY constraints can a single table have?
2. What happens by default if you try to DELETE a parent row that has child rows referencing it via a FOREIGN KEY?
3. What does a CHECK constraint like CHECK (EndDate > StartDate) accomplish?
4. How many NULL values does a UNIQUE constraint on a single column typically allow?
5. What is the effect of adding a constraint WITH NOCHECK to a table that already has data?
Was this page helpful?
You May Also Like
Indexes and Performance
Learn how clustered and nonclustered indexes speed up SQL Server queries, and how to read execution plans and statistics to diagnose slow queries.
Views and Schemas
Learn how SQL Server views package reusable queries and how schemas organize objects into logical namespaces with independent permissions.
Transactions and Locking
Understand how SQL Server groups statements into atomic transactions, uses locks to enforce isolation, and how deadlocks arise and get resolved.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics