DP-900: how to use constraints to ensure integrity in relational data

João Barros 27 de September de 2026 4 min read

I will teach how to use constraints in relational databases in the context of the DP-900. This skill explains how to ensure data integrity (for example, that relationships between tables remain correct) — a topic common on the exam and essential for real solutions in Azure SQL Database or Managed Instance.

What you need to know

Constraints are declarative rules applied to columns or tables to ensure that inserted data meets certain conditions. The main relational constraints you should know are:

PRIMARY KEY: uniquely identifies each row in a table; implies NOT NULL and UNIQUE.
FOREIGN KEY: ensures that a value in a column corresponds to an existing value in the referenced table (referential integrity).
UNIQUE: ensures that all values in a column (or set) are distinct.
NOT NULL: prevents null values in that column.
CHECK: defines a condition (boolean expression) that the row values must satisfy.

Simple example: in a sales database, the Customers table has a PRIMARY KEY CustomerID; the Orders table has OrderID (PK) and CustomerID with a FOREIGN KEY pointing to Customers(CustomerID). This way, you cannot create orders for non-existent customers.

How it works

Constraints are evaluated by the database engine whenever there are operations that modify data: INSERT, UPDATE, DELETE. If a rule is violated, the operation fails and an error is returned. In Azure SQL Database the behavior is identical to SQL Server on-premises.

Some important details:

FOREIGN KEYs can have ON DELETE/ON UPDATE actions (NO ACTION, CASCADE, SET NULL, SET DEFAULT) that define the effect when the referenced row is removed/updated.
UNIQUE can span multiple columns (multicolumn constraint) and can be supported by indexes for performance.
CHECK can use simple expressions: e.g., CHECK (Quantity > 0) — useful for lightweight business rules directly in the database.

In practice

Below are practical examples in T-SQL to create tables and constraints. Imagine you are creating two tables: Customers and Orders.

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name NVARCHAR(100) NOT NULL,
  Email NVARCHAR(256) UNIQUE
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT NOT NULL,
  OrderDate DATE NOT NULL,
  Quantity INT NOT NULL CHECK (Quantity > 0),
  CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
    ON DELETE CASCADE
);

Quick explanation:

CustomerID in Customers is the PRIMARY KEY: ensures uniqueness and identifies each customer.
Email is UNIQUE: prevents duplicates in customers' email addresses.
Orders.CustomerID is a FOREIGN KEY with ON DELETE CASCADE: when you delete a customer, all their orders are automatically removed — useful but has implications that I explain in "Common mistakes".
CHECK on Quantity prevents values ≤ 0.

Common mistakes

1) Using ON DELETE CASCADE without evaluating the impact: it can remove many rows unexpectedly. Before defining CASCADE, consider the business rules and the data recovery policy.

2) Relying only on constraints for complex rules: constraints are excellent for basic integrity, but complex business rules (for example, cumulative limits across tables, advanced temporal validations) may require triggers or application logic.

3) Creating too many constraints or indexes without considering performance: UNIQUE and FOREIGN KEY can cause overhead on write operations; test and evaluate trade-offs between integrity and performance.

How to practice

Practice these concepts by creating databases in Azure SQL Database (you can use a trial instance) or locally with SQL Server. Perform INSERT/UPDATE/DELETE operations to see how constraints behave and experiment with different ON DELETE/ON UPDATE options.

For DP-900 exam preparation use the official free Practice Assessment from Microsoft and the official study guide (both free). These official resources help you validate knowledge without resorting to unauthorized content.

In summary

Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK) ensure data integrity at the database level.
FOREIGN KEY ensures referential integrity; ON DELETE/ON UPDATE control cascading effects.
Constraints help prevent invalid data, but do not replace complex business logic.
Always test the impact of constraints on performance and write flows before applying them in production.