<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Creating and changing data
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 10 of 54 16 minBeginner

Constraints: primary keys, UNIQUE, CHECK and DEFAULT

Make the database enforce your rules: primary and foreign keys, UNIQUE, CHECK, DEFAULT and NOT NULL. Name them, add them to existing tables, and choose what happens when a parent row is deleted.

Application code has bugs, and data arrives from imports, scripts and other programs. Constraints are rules the database itself enforces on every INSERT and UPDATE, from every source. A rule written as a constraint cannot be forgotten.

ConstraintRuleExample
NOT NULLa value is requiredevery supplier has a Name
PRIMARY KEYunique and not NULL; identifies the rowSupplierId
UNIQUEno two rows share the valueno two suppliers share an Email
CHECKa condition must not be falseRating BETWEEN 1 AND 5
DEFAULTthe value used when none is givenRating defaults to 3
FOREIGN KEYthe value must exist in another tableCategoryId must be a real category

A table with every kind

Give each constraint a name with the CONSTRAINT keyword. A common pattern is PK_Table, UQ_Table_Column, CK_Table_Column, DF_Table_Column and FK_Child_Parent. Error messages then tell you exactly which rule failed, and you can drop or change the constraint later by name.

Query.sql
CREATE TABLE dbo.Suppliers (    SupplierId int           NOT NULL CONSTRAINT PK_Suppliers PRIMARY KEY,    Name       nvarchar(60)  NOT NULL,    Email      nvarchar(100) NULL CONSTRAINT UQ_Suppliers_Email UNIQUE,    Rating     tinyint       NOT NULL CONSTRAINT DF_Suppliers_Rating DEFAULT 3                             CONSTRAINT CK_Suppliers_Rating CHECK (Rating BETWEEN 1 AND 5),    CategoryId int           NULL CONSTRAINT FK_Suppliers_Categories                             REFERENCES dbo.Categories (CategoryId)); INSERT INTO dbo.Suppliers (SupplierId, Name, Email, Rating, CategoryId)VALUES (1, N'Northern Timber', N'sales@ntimber.example', 4, 3); INSERT INTO dbo.Suppliers (SupplierId, Name)VALUES (2, N'Pennine Fixings'); SELECT * FROM dbo.Suppliers;
Output
SupplierId  Name             Email                  Rating  CategoryId
----------  ---------------  ---------------------  ------  ----------
         1  Northern Timber  sales@ntimber.example       4           3
         2  Pennine Fixings  NULL                        3        NULL

Pennine Fixings got Rating 3 from the DEFAULT. Now try to break the rules:

Interactive

Which rule stops the row?

dbo.Suppliers already holds one row: (1, Northern Timber, sales@ntimber.example, 4, 3). Try to add another.

Statement.sql
INSERT INTO dbo.Suppliers (SupplierId, Name, Email, Rating, CategoryId)VALUES (2, N'Pennine Fixings', N'orders@pennine.example', 5, 4);
SupplierIdNameEmailRatingCategoryId
11Northern Timbersales@ntimber.example43
22Pennine Fixingsorders@pennine.example54
Every rule passes: a new id, a name, an email nobody else has, a rating from 1 to 5 and a category that exists.

Adding a constraint to an existing table

ALTER TABLE … ADD CONSTRAINT adds a rule later. SQL Server first checks the rows already there, and refuses if any break it. After that, every change is checked:

Query.sql
ALTER TABLE dbo.Products    ADD CONSTRAINT CK_Products_Price CHECK (Price > 0);GOUPDATE dbo.Products SET Price = -5 WHERE ProductId = 1;
Output
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Products_Price". The conflict occurred in database "BuildMart", table "dbo.Products", column 'Price'.

What happens to children when a parent goes?

By default a foreign key refuses to delete a parent row that still has children: you saw that when deleting a customer with orders. You can choose another action when you create the key:

ON DELETE …Deleting a parent row…
NO ACTION (default)fails while any child row points at it
CASCADEdeletes the child rows too
SET NULLsets the child’s foreign key column to NULL
SET DEFAULTsets it to the column’s DEFAULT
Query.sql
CREATE TABLE dbo.Quotes (    QuoteId    int  NOT NULL CONSTRAINT PK_Quotes PRIMARY KEY,    CustomerId int  NOT NULL);CREATE TABLE dbo.QuoteItems (    QuoteId   int NOT NULL CONSTRAINT FK_QuoteItems_Quotes              REFERENCES dbo.Quotes (QuoteId) ON DELETE CASCADE,    ProductId int NOT NULL,    Quantity  int NOT NULL,    CONSTRAINT PK_QuoteItems PRIMARY KEY (QuoteId, ProductId)); INSERT INTO dbo.Quotes (QuoteId, CustomerId) VALUES (1, 4), (2, 6);INSERT INTO dbo.QuoteItems (QuoteId, ProductId, Quantity) VALUES (1, 4, 30), (1, 5, 30), (2, 10, 3); DELETE FROM dbo.Quotes WHERE QuoteId = 1; SELECT * FROM dbo.QuoteItems;
Output
QuoteId  ProductId  Quantity
-------  ---------  --------
      2         10         3

Deleting quote 1 took its two items with it. CASCADE suits parts that make no sense on their own, such as the lines of a quote. It is dangerous for anything with a life of its own: you would not want deleting a category to delete its products.

Unique, but with many NULLs

SQL Server treats NULLs as equal for UNIQUE constraints, so it allows only one NULL. When the rule is “unique when present”, use a filtered unique index instead:

Query.sql
CREATE TABLE dbo.Contacts (    ContactId int           NOT NULL IDENTITY CONSTRAINT PK_Contacts PRIMARY KEY,    Name      nvarchar(60)  NOT NULL,    Email     nvarchar(100) NULL);CREATE UNIQUE INDEX UX_Contacts_Email ON dbo.Contacts (Email) WHERE Email IS NOT NULL; INSERT INTO dbo.Contacts (Name, Email)VALUES (N'Ben', NULL), (N'Grace', NULL), (N'Asha', N'asha@example.com'); SELECT * FROM dbo.Contacts;
Output
ContactId  Name   Email
---------  -----  ----------------
        1  Ben    NULL
        2  Grace  NULL
        3  Asha   asha@example.com
Quick check

A CHECK constraint is CHECK (Discount <= 50) and Discount allows NULL. What happens when you insert NULL?

Quick check

Which foreign key action fits Orders → Customers in a shop?

Practice

Stock can never be negative. Add a CHECK constraint named CK_Products_Stock that enforces this, then show that an UPDATE which takes 50 claw hammers from a stock of 40 is refused.

Show a hint

ALTER TABLE dbo.Products ADD CONSTRAINT … CHECK (Stock >= 0). Put GO between the ALTER and the UPDATE.

Show the solution
Solution.sql
ALTER TABLE dbo.Products    ADD CONSTRAINT CK_Products_Stock CHECK (Stock >= 0);GOUPDATE dbo.Products SET Stock = Stock - 50 WHERE ProductId = 1;
Output
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Products_Stock". The conflict occurred in database "BuildMart", table "dbo.Products", column 'Stock'.

Remember

  • Constraints are rules the database enforces on every change, from every program.
  • Name them (PK_, UQ_, CK_, DF_, FK_) so errors and later changes are clear.
  • CHECK rejects only FALSE; NULL passes, so pair it with NOT NULL when a value is required.
  • Foreign keys refuse orphan rows by default; ON DELETE CASCADE / SET NULL change that, use them deliberately.
  • UNIQUE allows one NULL in SQL Server; a filtered unique index (WHERE col IS NOT NULL) allows many.
0 of 54 lessons completed