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).

cover

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.

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. 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").

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.