DP-900 Practice Question: Identify considerations for relational data on Azure
A development team is designing a relational database for a hospital patient management system. They need to ensure that each patient's medical record number is unique and not null. Which database constraint should they use?
⚠ Common exam trap
Many candidates confuse UNIQUE with PRIMARY KEY, forgetting that UNIQUE allows NULL values (in most RDBMS implementations) and therefore does not satisfy the 'not null' requirement without an additional NOT NULL constraint.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
PRIMARY KEY
The PRIMARY KEY constraint enforces both uniqueness and non-nullability on the column(s) it is applied to. In a relational database, the medical record number is the natural candidate for the primary key of the Patient table because it uniquely identifies each patient and must always have a value. This directly meets the requirement that each patient's medical record number is unique and not null.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
FOREIGN KEY
Why it's wrong here
A FOREIGN KEY constraint is a referential integrity mechanism that links a column or columns in one table to the PRIMARY KEY or UNIQUE constraint of another table. It does not impose any requirement of uniqueness or non-null on the column itself; the same foreign key value can appear in many rows, and the column can contain NULL values unless a separate NOT NULL constraint is added. Therefore, it cannot by itself enforce the required uniqueness and non-null for the medical record number.
- ✗
CHECK
Why it's wrong here
A CHECK constraint enforces a Boolean predicate on column values, such as ensuring a number falls within a certain range or that a code matches a specific format. However, a CHECK constraint is evaluated per row and does not compare values across rows, so it cannot guarantee that each medical record number is unique or that the column disallows NULLs. It only validates that each individual value satisfies the condition, which is insufficient for this requirement.
- ✗
UNIQUE
Why it's wrong here
A UNIQUE constraint does guarantee that all values in the column are distinct, which is the uniqueness part of the requirement. However, in most relational database systems, including SQL Server, a UNIQUE constraint permits exactly one NULL value (or multiple NULLs in some databases), because NULL is treated as unknown and does not equal another NULL. Since the scenario explicitly requires the medical record number to be non-null, a UNIQUE constraint alone does not satisfy the combined uniqueness and non-null requirement.
- ✓
PRIMARY KEY
Why this is correct
A PRIMARY KEY constraint enforces both uniqueness and non-nullness in a single declaration: the column cannot contain any NULL values, and every value must be unique across the table. This makes it the ideal choice for a medical record number, which must always be present and uniquely identify each patient. Additionally, a primary key automatically creates an index (typically clustered) to support fast lookups and enforce data integrity.
Go deeper
Related to this question
Learn chapter
Relational Data Concepts
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
Primary key
A primary key is a unique identifier for each record in a database table, ensuring that no two rows have the same value in that column.
About these practice questions
This DP-900 question is part of Courseiva's 820-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.