(+351) 21 24 10006  ·  info@bconcepts.pt
Carnaxide, Lisbon

DP-900: how to understand and use primary and foreign keys

João Barros 14 de November de 2026 4 min read

I will teach a DP-900 skill: how to understand and use primary and foreign keys in relational databases on Azure. This topic is essential for the exam because it shows you can model relationships and ensure referential integrity, and it is equally practical when building secure and consistent solutions in Azure SQL Database or in managed instances.

What you need to know

Primary Key (Primary Key): is one or more fields that uniquely identify each row in a table. It cannot contain NULLs and typically has a clustered or nonclustered index associated for efficiency. Example: in a Customers table, CustomerID is the PK.

Foreign Key (Foreign Key): is a field in one table that points to the PK (or a candidate key) in another table, creating a relationship between the two. It ensures referential integrity — for example, an Order has a CustomerID that references Customers(CustomerID).

Why this matters: without well-defined keys, we risk duplication, inconsistency and difficulties with JOIN queries. In Azure SQL Database, the implementation and constraints behave the same as in SQL Server, so local practice is directly applicable in the cloud.

How it works (step-by-step)

Practical example: two simple tables, Customers and Orders. First we create Customers with a PK. Then we create Orders with an FK that references Customers.

-- criar tabela Customers com PK CREATE TABLE Customers (   CustomerID INT IDENTITY(1,1) PRIMARY KEY,   Name NVARCHAR(100) NOT NULL,   Email NVARCHAR(255) );  -- criar tabela Orders com FK para Customers CREATE TABLE Orders (   OrderID INT IDENTITY(1,1) PRIMARY KEY,   OrderDate DATETIME2 NOT NULL,   CustomerID INT NOT NULL,   Amount DECIMAL(10,2) NOT NULL,   CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)     REFERENCES Customers(CustomerID)     ON DELETE NO ACTION     ON UPDATE CASCADE ); 

Key notes about the ON DELETE / ON UPDATE options:

  • NO ACTION / RESTRICT: prevents deletion/update if references exist — default behavior appropriate when you do not want data loss.
  • CASCADE: propagates deletions/updates from the referenced table; useful for dependent relationships (e.g.: orders that should disappear if the customer is removed), but dangerous if used without care.
  • SET NULL / SET DEFAULT: sets the FK to NULL or a default value when the referenced row is deleted; only works if the column allows NULLs or there is a default value.

In practice: common operations

Insert rows respecting referential integrity:

-- inserir cliente e encomenda válida INSERT INTO Customers (Name, Email) VALUES ('Ana Silva', 'ana@example.com'); DECLARE @cid INT = SCOPE_IDENTITY();  INSERT INTO Orders (OrderDate, CustomerID, Amount) VALUES (SYSDATETIME(), @cid, 120.00); 

Attempting to insert an Order with a CustomerID that does not exist will fail:

-- isto gera erro devido ao constraint FK_Orders_Customers INSERT INTO Orders (OrderDate, CustomerID, Amount) VALUES (SYSDATETIME(), 9999, 50.00); 

Deleting a customer with orders will fail if ON DELETE is NO ACTION:

-- gera erro se existirem Orders para o cliente DELETE FROM Customers WHERE CustomerID = @cid; 

If you choose ON DELETE CASCADE, deleting the customer automatically deletes the associated Orders — useful but requires caution to avoid accidental cascade removals.

Common mistakes

1) Defining an FK to a column without an index in the referenced table: although it works, it can harm performance in JOIN operations and in integrity checks; ensure the referenced column is a PK or has a unique index.

2) Using ON DELETE CASCADE without evaluating the impact: it can remove many records unexpectedly. Avoid automatic cascades in complex relationships without review transactions.

3) Allowing NULLs on the FK unnecessarily: this creates optional relationships and can complicate business logic. If the relationship is mandatory, make the column NOT NULL and apply the constraint.

How to practice

Practice these concepts by creating local databases (SQL Server Developer) and then deploy them to an Azure SQL Database instance — the behavior of constraints is the same. For exam-oriented practice, use the OFFICIAL Microsoft Practice Assessment (free) and consult the official Microsoft study guide (also free). These sources help you verify knowledge without resorting to prohibited material.

In summary

  • Primary key uniquely identifies each row and does not accept NULLs; it is fundamental for performance and integrity.
  • Foreign key creates relationships between tables and ensures referential integrity via constraints (NO ACTION, CASCADE, SET NULL...).
  • Choose ON DELETE / ON UPDATE options based on business rules and test effects in transactions before applying in production.
  • Practice locally and in Azure SQL Database; use the official Practice Assessment and the Microsoft study guide for preparation.