This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement database objects
      --> Design and implement database constraints, including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Database constraints are one of the most important mechanisms for maintaining data integrity in SQL Server and Azure SQL Database. A constraint is a rule that SQL Server automatically enforces whenever data is inserted, updated, or deleted. Instead of relying solely on application logic, constraints ensure that only valid, consistent, and meaningful data is stored in the database.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• The purpose of each constraint type
• When to use each constraint
• How constraints enforce data integrity
• How constraints affect performance and database design
• Best practices for implementing constraints

Proper use of constraints improves application reliability, reduces programming errors, and helps maintain high-quality data for reporting, analytics, and AI-enabled applications.

What Are Database Constraints?

A database constraint is a rule applied to a table or column that restricts the type of data that can be stored.

Constraints help ensure:

• Entity integrity
• Referential integrity
• Domain integrity
• Data consistency
• Data accuracy

Without constraints, invalid or inconsistent data can easily enter the database, leading to application errors and unreliable reports.

Types of Constraints

The primary constraint types covered on the DP-800 exam include:

PRIMARY KEY Constraint

Purpose

A PRIMARY KEY uniquely identifies every row in a table.

Characteristics:

• Values must be unique.
• NULL values are not allowed.
• Only one PRIMARY KEY can exist per table.
• SQL Server automatically creates a unique index to enforce the constraint (clustered by default unless otherwise specified).

Example

CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

Every customer must have a unique CustomerID.

Composite Primary Keys

A PRIMARY KEY may consist of multiple columns.

Example:

CREATE TABLE OrderDetails
(
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

The combination of OrderID and ProductID must be unique.

Composite keys are commonly used in junction (bridge) tables.

Natural vs. Surrogate Keys

Natural Key

A value that already exists in the business domain.

Examples:

• Social Security Number
• Email address
• Vehicle Identification Number (VIN)

Advantages:

• Business meaning
• No additional column required

Disadvantages:

• May change
• Can be lengthy
• May not always be unique globally

Surrogate Key

An artificial identifier created solely for the database.

Example:

CustomerID INT IDENTITY(1,1)

Advantages:

• Stable
• Compact
• Efficient for indexing
• Easy to join

Most SQL Server applications use surrogate keys as PRIMARY KEY values.

FOREIGN KEY Constraint

Purpose

A FOREIGN KEY maintains referential integrity between related tables.

It ensures that values in one table correspond to existing values in another.

Example

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
);

An order cannot reference a customer that does not exist.

Referential Integrity

Foreign keys prevent:

• Orphan records
• Invalid relationships
• Accidental inconsistencies

Example:

Customers

Orders

CustomerID 5 cannot be inserted because it does not exist.

Cascade Actions

Foreign keys support optional cascading behavior.

CASCADE DELETE

Deleting the parent automatically deletes child rows.

Example:

FOREIGN KEY(CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE

CASCADE UPDATE

Updates to the parent key automatically update child rows.

Example:

ON UPDATE CASCADE

SET NULL

When the parent row is deleted, the child foreign key becomes NULL.

ON DELETE SET NULL

Requires the foreign key column to allow NULL values.

SET DEFAULT

When the parent row is deleted, the child receives its default value.

ON DELETE SET DEFAULT

Requires a DEFAULT constraint on the foreign key column.

NO ACTION (Default)

SQL Server prevents deletion or update if related child rows exist.

This is the default behavior.

UNIQUE Constraint

Purpose

A UNIQUE constraint prevents duplicate values while allowing the column to serve as an alternate key.

Unlike a PRIMARY KEY:

• Multiple UNIQUE constraints may exist in a table.
• A UNIQUE constraint is not the table’s primary identifier.

Example

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    EmailAddress NVARCHAR(255) UNIQUE
);

No two employees can have the same email address.

UNIQUE and NULL Values

A UNIQUE constraint allows at most one NULL value in SQL Server.

Example:

A second NULL violates the UNIQUE constraint.

Composite UNIQUE Constraints

Example:

UNIQUE (FirstName, LastName, BirthDate)

Only the combination must be unique.

CHECK Constraint

Purpose

A CHECK constraint restricts allowable values.

It enforces business rules directly within the database.

Example

CHECK (Salary > 0)

Negative salaries cannot be inserted.

Multiple Conditions

Example:

CHECK
(
Age >= 18
AND Age <= 65
)

Character Validation

Example:

CHECK
(
Status IN
('New','Active','Closed')
)

Only approved values are permitted.

Date Validation

Example:

CHECK
(
HireDate <= GETDATE()
)

Preventing future hire dates may be appropriate depending on business requirements. (Be aware that nondeterministic functions such as GETDATE() can affect certain indexing scenarios, but they are permitted in CHECK constraints.)

DEFAULT Constraint

Purpose

DEFAULT automatically supplies a value when none is specified.

Example

Status NVARCHAR(20)
DEFAULT 'Pending'

If Status is omitted:

INSERT INTO Orders
(OrderID)
VALUES
(101);

SQL Server inserts:

Pending

Using Functions

Defaults often use built-in functions.

Example:

CreatedDate DATETIME2
DEFAULT SYSDATETIME()

Other common examples include:

DEFAULT NEWID()

DEFAULT SUSER_SNAME()

Named Constraints

Rather than allowing SQL Server to generate names automatically, explicitly naming constraints simplifies administration.

Example:

CONSTRAINT PK_Customers
PRIMARY KEY(CustomerID)

Benefits include:

• Easier troubleshooting
• Easier scripting
• Easier deployment
• Easier maintenance

Adding Constraints to Existing Tables

Example:

ALTER TABLE Employees
ADD CONSTRAINT CK_Salary
CHECK (Salary > 0);

Example:

ALTER TABLE Employees
ADD CONSTRAINT UQ_Email
UNIQUE (EmailAddress);

Removing Constraints

Example:

ALTER TABLE Employees
DROP CONSTRAINT CK_Salary;

Constraint Evaluation

Constraints are enforced whenever data modifications occur.

Examples include:

• INSERT
• UPDATE
• MERGE

If a constraint is violated:

• The statement fails.
• The transaction may be rolled back depending on transaction handling.
• SQL Server returns an error.

Constraints vs. Indexes

Although related, constraints and indexes serve different purposes.

For example:

• PRIMARY KEY creates a unique index.
• UNIQUE creates a unique index.
• CHECK does not create an index.
• DEFAULT does not create an index.

Constraints and AI-Enabled Applications

AI applications depend on high-quality, trustworthy data.

Constraints help ensure:

• Clean training data
• Accurate feature engineering
• Reliable vector metadata
• Consistent model inputs
• Reduced preprocessing effort

For example:

• CHECK constraints can prevent impossible values (such as negative ages).
• FOREIGN KEY constraints ensure relationships remain valid.
• DEFAULT constraints automatically populate timestamps used for AI event tracking.
• UNIQUE constraints prevent duplicate identities that could bias analytics.

Best Practices

• Define PRIMARY KEY constraints for every table.
• Prefer surrogate keys for most transactional systems.
• Use FOREIGN KEY constraints to enforce relationships instead of relying solely on application logic.
• Use UNIQUE constraints for alternate keys such as email addresses or account numbers.
• Apply CHECK constraints to enforce business rules whenever practical.
• Use DEFAULT constraints for common initial values such as timestamps and statuses.
• Explicitly name constraints using consistent naming conventions.
• Avoid disabling constraints except during carefully managed bulk loading scenarios.
• Review cascade actions carefully to avoid unintended data loss.
• Validate existing data before adding new constraints to production tables.

Common Exam Tips

For the DP-800 exam, remember these important facts:

• Every table can have only one PRIMARY KEY, but it may consist of one or more columns.
• A PRIMARY KEY cannot contain NULL values.
• A FOREIGN KEY enforces referential integrity between related tables.
• NO ACTION is the default behavior for FOREIGN KEY delete and update operations.
• A table can have multiple UNIQUE constraints.
• A CHECK constraint enforces domain or business rules.
• A DEFAULT constraint supplies a value only when one is not explicitly provided during an INSERT.
• PRIMARY KEY and UNIQUE constraints automatically create unique indexes to enforce uniqueness.
• CHECK and DEFAULT constraints do not create indexes.

Practice Exam Questions

Question 1

A database designer needs to ensure that every customer record has a unique identifier that cannot contain NULL values. Which constraint should be used?

A. UNIQUE

B. CHECK

C. PRIMARY KEY

D. FOREIGN KEY

Answer: C

Explanation: A PRIMARY KEY uniquely identifies every row in a table and does not allow NULL values. Each table can have only one PRIMARY KEY.

Question 2

An Orders table contains a CustomerID column that must always reference an existing customer in the Customers table. Which constraint enforces this relationship?

A. FOREIGN KEY

B. DEFAULT

C. UNIQUE

D. CHECK

Answer: A

Explanation: A FOREIGN KEY enforces referential integrity by ensuring that values in one table correspond to existing values in another table.

Question 3

A company wants every new order to receive a status of “Pending” unless another value is explicitly supplied during insertion. Which constraint should be implemented?

A. CHECK

B. UNIQUE

C. FOREIGN KEY

D. DEFAULT

Answer: D

Explanation: A DEFAULT constraint automatically assigns a value when one is not provided in an INSERT statement.

Question 4

A database must prevent employees from having duplicate email addresses, but EmployeeID already serves as the table’s PRIMARY KEY. Which constraint should be added to the EmailAddress column?

A. PRIMARY KEY

B. DEFAULT

C. UNIQUE

D. CHECK

Answer: C

Explanation: A UNIQUE constraint enforces uniqueness for a column without making it the table’s primary identifier.

Question 5

Which constraint is best suited to ensure that an employee’s salary is always greater than zero?

A. CHECK

B. UNIQUE

C. DEFAULT

D. FOREIGN KEY

Answer: A

Explanation: A CHECK constraint validates that column values satisfy a specified logical condition, such as Salary > 0.

Question 6

Which statement about PRIMARY KEY constraints is correct?

A. A table can contain multiple PRIMARY KEY constraints.

B. PRIMARY KEY values may contain NULL values.

C. PRIMARY KEY constraints automatically enforce uniqueness and prevent NULL values.

D. PRIMARY KEY constraints cannot be referenced by FOREIGN KEY constraints.

Answer: C

Explanation: A PRIMARY KEY enforces uniqueness and does not permit NULL values. It is also commonly referenced by FOREIGN KEY constraints.

Question 7

What is the default action if a parent row referenced by a FOREIGN KEY is deleted without specifying any cascade option?

A. CASCADE

B. SET NULL

C. SET DEFAULT

D. NO ACTION

Answer: D

Explanation: Unless another referential action is specified, SQL Server uses NO ACTION, preventing deletion when related child rows exist.

Question 8

A junction table contains OrderID and ProductID, and each combination must be unique. Which design is most appropriate?

A. Add separate UNIQUE constraints to each column.

B. Create a composite PRIMARY KEY using OrderID and ProductID.

C. Create a DEFAULT constraint on both columns.

D. Use a CHECK constraint to compare the values.

Answer: B

Explanation: A composite PRIMARY KEY ensures that the combination of OrderID and ProductID is unique while allowing each individual value to appear multiple times as part of different combinations.

Question 9

Which statement best describes a UNIQUE constraint?

A. It prevents duplicate values and can be defined multiple times within a table.

B. It automatically creates foreign key relationships.

C. It validates numeric ranges.

D. It supplies default values during inserts.

Answer: A

Explanation: A table may contain multiple UNIQUE constraints, each preventing duplicate values in a column or combination of columns.

Question 10

Why are database constraints particularly valuable in AI-enabled database solutions?

A. They automatically generate machine learning models.

B. They improve graphics rendering performance.

C. They eliminate the need for indexes.

D. They help ensure high-quality, consistent data for analytics and AI workloads.

Answer: D

Explanation: Constraints improve data quality by enforcing consistency, valid relationships, and business rules, which reduces data cleansing and improves the reliability of AI models and analytical processes.

Go to the DP-800 Exam Prep Hub main page