SQL Server Constraints with Examples

Constraints are rules the database itself enforces — no application bug, script, or manual UPDATE can violate them. They're your last and most reliable line of defense for data integrity. This tutorial creates a table carrying all six constraint types, then deliberately violates each one so you can see the exact errors SQL Server raises (all outputs real).

All six on one table

CREATE TABLE Products (
    ProductId  INT IDENTITY
               CONSTRAINT PK_Products PRIMARY KEY,
    Sku        VARCHAR(20) NOT NULL
               CONSTRAINT UQ_Products_Sku UNIQUE,
    Name       NVARCHAR(100) NOT NULL,
    Price      DECIMAL(10,2) NOT NULL
               CONSTRAINT CK_Products_Price CHECK (Price > 0),
    Stock      INT NOT NULL
               CONSTRAINT DF_Products_Stock DEFAULT 0,
    CategoryId INT NULL
               CONSTRAINT FK_Products_Categories
               REFERENCES Categories(CategoryId)
);

Name your constraints (PK_, UQ_, CK_, DF_, FK_ prefixes are the common convention). Unnamed ones get generated names like FK__Orders__Customer__398D8EEE — miserable to reference in migrations and error logs.

PRIMARY KEY

One per table; uniquely identifies each row; implies NOT NULL; creates a unique (by default clustered) index. INT IDENTITY primary keys auto-number rows:

INSERT INTO Products (Sku, Name, Price) VALUES ('BK-001', 'Road Bike', 539.99);
SELECT ProductId, Sku, Price, Stock FROM Products;

ProductId  Sku     Price   Stock
---------  ------  ------  -----
1          BK-001  539.99  0

Stock arrived as 0 — the DEFAULT constraint filled it since the INSERT didn't mention it.

Composite keys (PRIMARY KEY (OrderId, ProductId)) are standard for join tables. And a subtle fact: IDENTITY values are consumed even by failed inserts — gaps in the sequence are normal and harmless. Need gap-free, custom-formatted numbers instead (invoice numbers, order codes)? IDENTITY won't give you that — see generating sequence numbers in a SELECT query.

UNIQUE

Uniqueness for natural keys that aren't the primary key — SKUs, emails, usernames:

INSERT INTO Products (Sku, Name, Price) VALUES ('BK-001', 'Duplicate', 10);

Msg 2627, Level 14, State 1
Violation of UNIQUE KEY constraint 'UQ_Products_Sku'. Cannot insert
duplicate key in object 'dbo.Products'. The duplicate key value is (BK-001).

The error names the constraint and the offending value — one reason naming matters. Unlike PRIMARY KEY, UNIQUE allows one NULL (SQL Server treats a second NULL as a duplicate). Need "unique among non-null values"? Use a filtered unique index: CREATE UNIQUE INDEX ... WHERE Sku IS NOT NULL.

CHECK

Arbitrary row-level rules:

INSERT INTO Products (Sku, Name, Price) VALUES ('BK-002', 'Free Bike', 0);

Msg 547, Level 16, State 0
The INSERT statement conflicted with the CHECK constraint "CK_Products_Price".
The conflict occurred in database "GeekStoreSQL", table "dbo.Products", column 'Price'.

CHECKs can reference multiple columns of the same row — CHECK (EndDate > StartDate), CHECK (Discount BETWEEN 0 AND 100), CHECK (Status IN ('Draft','Published','Archived')). They cannot query other rows or tables; that's trigger/application territory. A CHECK can call a deterministic scalar user-defined function for reuse across tables, but keep it cheap — it runs on every insert and update that touches the column. One caveat: a CHECK passes when the expression is NULL — Price > 0 doesn't reject a NULL price; NOT NULL does.

DEFAULT

A value used when the column is omitted. Beyond constants: DEFAULT GETDATE() for created-at stamps, DEFAULT NEWID() for GUIDs, DEFAULT SYSTEM_USER for audit columns. DEFAULT only fires on omission — an explicit NULL in the INSERT still inserts NULL.

FOREIGN KEY

Referential integrity — a child value must exist in the parent:

INSERT INTO Orders (CustomerId, OrderDate, Amount) VALUES (999, '2026-07-01', 10);

Msg 547, Level 16, State 0
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK__Orders__Customer__398D8EEE". The conflict occurred in database
"GeekStoreSQL", table "dbo.Customers", column 'CustomerId'.

(That auto-generated name is the naming lesson taught by example.) FKs also block deleting a parent that has children, unless you declare the behavior:

CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
    ON DELETE CASCADE      -- delete children with the parent
 -- ON DELETE SET NULL     -- orphan them explicitly
 -- default: NO ACTION     -- refuse the delete

Choose deliberately — CASCADE on the wrong relationship turns "delete one customer" into "delete their invoices". And index your FK columns: SQL Server doesn't do it automatically, and every join plus every parent-delete check hits that column.

NOT NULL

The simplest and most under-used constraint. Every nullable column is a promise that every reader handles NULL — make columns NOT NULL unless NULL genuinely means something ("not yet shipped", "no manager").

What constraints cost (and buy) at query time

Constraints aren't just validation — the optimizer leans on them:

• PRIMARY KEY and UNIQUE are backed by an index, so they speed up the exact lookups you'd expect and give the optimizer a uniqueness guarantee it can use to simplify plans (skip a redundant DISTINCT, avoid extra aggregation).
• A trusted FOREIGN KEY lets the optimizer eliminate a join entirely when a query touches the parent table's columns but doesn't actually need them — it already knows every child row has a matching parent.
• A trusted CHECK can prune partitions or entire branches of a plan. CHECK (Status IN ('Draft','Published','Archived')) tells the optimizer a query filtering Status = 'Cancelled' can return zero rows without touching the table.

None of that applies to a FOREIGN KEY column itself unless you index it — SQL Server never does this automatically:

CREATE INDEX IX_Orders_CustomerId ON Orders (CustomerId);

Skip it and every join from Orders to Customers, plus every check SQL Server runs before letting you delete a customer, scans the full Orders table.

Constraints and bulk loads

BULK INSERT and SqlBulkCopy skip CHECK and FOREIGN KEY validation by default — they're built for speed, and per-row constraint checks defeat the purpose. Two consequences:

• Bad or orphaned rows can land in the table silently. If you're loading from C# with SqlBulkCopy, validate in the application first, or load into a staging table and validate with a set-based query before moving rows into the real table.
• Every constraint the bulk load bypassed comes out marked not trusted — same as WITH NOCHECK — until you explicitly WITH CHECK CHECK CONSTRAINT it, even if every row it skipped was actually valid.

If you're staging with a table variable instead of a real table, know the limits going in — table variables support PRIMARY KEY and UNIQUE but not a real FOREIGN KEY, and no statistics get built on them. The temp table vs. table variable comparison covers this in detail.

How EF Core maps to these constraints

[Required] and the other DataAnnotations attributes cover the simple cases, but CHECK constraints, named FKs, and delete behavior all need Fluent API — see configuring entity relationships with Fluent API for the FK side specifically. Whichever way you configure it, a migration only adds the constraint — it doesn't retroactively fix data that already violates it, same as the raw ALTER TABLE below.

Adding constraints to existing tables

ALTER TABLE Products ADD CONSTRAINT CK_Products_Stock CHECK (Stock >= 0);

Existing data must comply or the ALTER fails. To adopt a constraint without validating history:

ALTER TABLE Products WITH NOCHECK
    ADD CONSTRAINT CK_Products_Stock CHECK (Stock >= 0);

Old bad rows stay; new writes are checked. The catch: the constraint is marked not trusted, and the optimizer stops using it for plan simplification — clean the data and WITH CHECK CHECK CONSTRAINT when you can.

Find everything in place with:

SELECT name, type_desc FROM sys.objects
WHERE type IN ('PK','UQ','C','D','F') AND parent_object_id = OBJECT_ID('Products');

Constraints vs application validation

Do both, for different reasons. Application validation (like DataAnnotations) gives users friendly, immediate messages. Constraints guarantee the rule against every writer — the app, the ETL job, the intern with SSMS. When they disagree, the constraint wins, which is exactly what you want: the TRY/CATCH post shows how procedures turn constraint violations into clean error handling.