SQL Server Constraints – Primary Key, Foreign Key, Unique and Check
Quick Answer: SQL Server constraints are rules that protect data inside database tables. Primary Key, Foreign Key, UNIQUE and CHECK help prevent duplicate, unrelated and invalid records.
1. Why Do We Need SQL Server Constraints?
Imagine Priya manages an online grocery shop with 10,000 customers. Every customer has an email address, and every order belongs to a customer.
Some customers may register twice. An order may also contain an invalid customer ID or negative amount.
How can Priya prevent these problems directly inside her database?
The problem without constraints
We could create Customers and Orders tables without validation rules. SQL Server would then accept many incorrect records.
Example 1: Grocery tables without constraints
This SQL Server script intentionally inserts invalid business records.
CREATE TABLE Customers
(
CustomerId INT,
CustomerName VARCHAR(50),
Email VARCHAR(100)
);
CREATE TABLE Orders
(
OrderId INT,
CustomerId INT,
Amount DECIMAL(10, 2)
);
INSERT INTO Customers
VALUES
(101, 'Priya', 'priya@shop.com'),
(101, 'Ravi', 'ravi@shop.com'),
(102, 'Arjun', 'priya@shop.com');
INSERT INTO Orders
VALUES
(501, 999, -500);
SELECT * FROM Customers;
SELECT * FROM Orders;
Expected output: Customers
CustomerId CustomerName Email 101 Priya priya@shop.com 101 Ravi ravi@shop.com 102 Arjun priya@shop.com
Expected output: Orders
OrderId CustomerId Amount 501 999 -500.00
The script runs, but the stored information has several problems.
- CustomerId 101 appears more than once.
- Two customers share the same email address.
- Order 501 references customer 999, who does not exist.
- The order amount is negative.
Now imagine Priya handling thousands of customer registrations and orders. These problems can affect reports and business operations.
We need database-level rules to protect every inserted record. SQL Server provides constraints for this purpose.
The solution: SQL Server Constraints
Constraint = a rule that controls which values a table accepts.
Primary Key = uniquely identifies each record in a table.
Foreign Key = connects records between related tables.
UNIQUE = prevents duplicate values in a column or column combination.
CHECK = accepts values only when a specified condition is true or unknown.
We will create four constraints for Priya's grocery database. Each constraint protects a different part of her business data.
Animation 1: How SQL Server validates grocery records
Follow Priya's customer and order through four database rules.
SQL Server checks the customer identity, email, customer reference and order amount before accepting the records.
2. Implement SQL Server Constraints in T-SQL
Let's implement all four constraints using Priya's grocery database.
1. Primary Key Constraint
Primary Key = a column or column combination that uniquely identifies each row.
Priya needs every customer and order to have a unique ID.
Create a table with a Primary Key
CustomerId identifies each customer in the Customers table.
CREATE TABLE Customers
(
CustomerId INT
CONSTRAINT PK_Customers PRIMARY KEY,
CustomerName VARCHAR(50)
);
The PK_Customers constraint gives each customer a unique identity.
SQL Server does not allow duplicate or NULL Primary Key values.
2. Foreign Key Constraint
Foreign Key = a column that references a candidate key in another table.
Priya must ensure every order belongs to an existing customer.
Create a table with a Foreign Key
CustomerId connects Orders with the Customers table.
CREATE TABLE Orders
(
OrderId INT
CONSTRAINT PK_Orders PRIMARY KEY,
CustomerId INT,
Amount DECIMAL(10, 2),
CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerId)
REFERENCES Customers(CustomerId)
);
Notice the REFERENCES keyword connects both tables.
SQL Server rejects an order when its customer does not exist.
3. UNIQUE Constraint
UNIQUE = a rule that prevents duplicate values in selected columns.
Priya wants every customer to register using a different email address.
Add a UNIQUE constraint to Customers
The Email column must contain unique non-NULL values.
ALTER TABLE Customers
ADD CONSTRAINT UQ_Customers_Email
UNIQUE (Email);
The ALTER TABLE statement adds the new rule.
SQL Server rejects another customer using the same email address.
Important: SQL Server allows one NULL in a single-column UNIQUE constraint. Use NOT NULL when every customer must provide an email address.
4. CHECK Constraint
CHECK = a rule that tests a condition before accepting a row.
Priya does not want orders with zero or negative amounts.
Add a CHECK constraint to Orders
The Amount column must contain a value greater than zero.
ALTER TABLE Orders
ADD CONSTRAINT CK_Orders_Amount
CHECK (Amount > 0);
The condition Amount > 0 allows positive amounts.
SQL Server rejects zero and negative amounts. A NULL value needs a separate NOT NULL rule.
Important: A CHECK constraint does not replace NOT NULL. SQL Server treats an UNKNOWN CHECK result as acceptable, so NULL values need separate protection.
3. Quick Summary
Primary Key vs Foreign Key vs UNIQUE vs CHECK
| Constraint | Purpose | Grocery example |
|---|---|---|
| PRIMARY KEY | Identifies each row uniquely. | CustomerId |
| FOREIGN KEY | Maintains relationships. | Orders.CustomerId |
| UNIQUE | Prevents duplicate values. | Customer email |
| CHECK | Enforces a condition. | Amount > 0 |
The important terms to remember
- Primary Key: Unique identity for every row; cannot contain NULL.
- Foreign Key: Maintains valid references between related tables.
- UNIQUE: Prevents duplicate key values.
- CHECK: Enforces a logical condition on column values.
- Constraint name: A meaningful name used to identify a database rule.
4. Common Mistakes and Best Practices
1. Creating a Foreign Key without a matching Primary Key
A Foreign Key must reference an eligible candidate key in another table.
Better approach: Create the referenced Primary Key or UNIQUE constraint first.
2. Assuming UNIQUE and Primary Key are identical
A table can have multiple UNIQUE constraints, but only one Primary Key constraint.
Better approach: Use Primary Key for row identity and UNIQUE for additional business rules.
3. Assuming CHECK prevents NULL values
A CHECK condition alone does not guarantee that a column contains a value.
Better approach: Combine CHECK with NOT NULL when values are mandatory.
Design tip: Give constraints meaningful names such as PK_Customers, FK_Orders_Customers, UQ_Customers_Email and CK_Orders_Amount. This makes database maintenance easier.
5. How SQL Server Constraints Come Up in Interviews
Question 1: What is the difference between Primary Key and UNIQUE?
What the interviewer expects: Explain uniqueness and NULL handling.
Strong answer: "A Primary Key uniquely identifies each row and cannot contain NULL. A table can have only one Primary Key, but multiple UNIQUE constraints. SQL Server allows one NULL in a single-column UNIQUE constraint."
Question 2: What happens when a Foreign Key references a missing customer?
What the interviewer expects: Explain referential integrity.
Strong answer: "SQL Server rejects the insert or update when the referenced customer does not exist. This protects the relationship between Customers and Orders."
Question 3: Can a table have multiple Primary Keys?
What the interviewer expects: Understand Primary Key rules.
Strong answer: "A table can have only one Primary Key constraint. However, that Primary Key can contain multiple columns. We call this a composite Primary Key."
6. Hands-On Practice
Beginner: School database
Create Students and Marks tables. Add a Primary Key and Foreign Key to connect them.
Expected result: Marks must reference an existing student.
Intermediate: Hospital database
Create a Patients table with a unique email address. Add a CHECK constraint for patient age.
Hint: Use UNIQUE and CHECK constraints.
Advanced: Banking database
Create Customers and BankTransactions tables with suitable constraints.
- Use a Primary Key for transaction identity.
- Use a Foreign Key for customer relationships.
- Prevent duplicate transaction reference numbers.
- Allow only positive transaction amounts.
Challenge: Try inserting invalid records and observe SQL Server's errors.
7. Next Steps and Community
Previous article: Creating Databases and Tables – CREATE, ALTER and DROP
Next article: SQL SELECT Statement – Retrieving Data from Tables
For more details, read the official Microsoft documentation:
Try Priya's grocery database example. Add more constraints and test invalid records. Share your SQL scripts and discuss your solutions with other learners.