mediumMultiple Choice

FC0-U71 Practice Question: Refer to the exhibit

Exhibit

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Refer to the exhibit. A user attempts to insert a row into Orders with a CustomerID that does not exist in the Customers table. What is the expected outcome?

⚠ Common exam trap

It's easy for candidates to assume the database will silently accept the row or create a placeholder customer, failing to recognize that foreign key constraints enforce strict referential integrity and cause the statement to fail with an error.

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

✓

The INSERT statement fails with a constraint violation

The INSERT statement fails with a constraint violation because the CustomerID column in the Orders table is defined as a foreign key referencing the CustomerID primary key in the Customers table. When a user attempts to insert a row with a CustomerID that does not exist in the Customers table, the relational database management system (RDBMS) enforces referential integrity and rejects the operation, raising a foreign key constraint violation error.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
The row is inserted but with a warning
Why it's wrong here
The foreign key constraint aborts the insert with an error, so no row is written and no warning is issued. SQL treats referential integrity violations as failures, not advisory notices. It tempts because some constraints, such as nullable columns or check warnings in certain tools, permit insertion with alerts, but foreign keys do not.
✗
The row is inserted successfully
Why it's wrong here
Violates foreign key constraint.
✗
The Customers table automatically creates a new customer
Why it's wrong here
A foreign key constraint on Orders.CustomerID rejects the insert; nothing creates a parent row implicitly. Referential integrity only validates that the referenced Customers row already exists. It tempts because cascading operations can propagate changes between related tables, but those act on existing rows, not on missing parents.
✓
The INSERT statement fails with a constraint violation
Why this is correct
The CustomerID column carries a foreign key referencing Customers, so the database engine enforces referential integrity. Inserting a value absent from the parent table violates that constraint, causing the INSERT to be rejected rather than silently accepted.

Go deeper

3 more ways this is tested on FC0-U71

These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.

Variation 1. A company's customer database contains a table named 'Orders' with columns: OrderID, CustomerID, ProductID, Quantity, OrderDate. The company wants to enforce that every CustomerID in 'Orders' must exist in the 'Customers' table. Which database constraint should be added to the 'Orders' table?

medium

A.Check constraint on CustomerID
B.Primary key on CustomerID
✓ C.Foreign key on CustomerID referencing Customers
D.Unique constraint on CustomerID

Why C: A foreign key constraint on the CustomerID column in the Orders table ensures referential integrity by requiring that every value entered in that column must match a CustomerID value in the Customers table. This prevents orphan records and maintains consistency between the two tables, which is the standard relational database approach for enforcing such a relationship.

Variation 2. A sales database has a Customers table with CustomerID as the primary key and an Orders table that includes CustomerID. Which type of relationship is typically established between Customers and Orders?

medium

A.One-to-one
B.Many-to-many
C.Unrelated
✓ D.One-to-many

Why D: A single customer can place many orders, but each order belongs to exactly one customer, which is the definition of a one-to-many relationship. The CustomerID foreign key in the Orders table references the primary key in Customers, enforcing that each order is tied to one customer while allowing a customer to have multiple orders.

Variation 3. A database has a table 'Orders' with columns OrderID (primary key), CustomerID, OrderDate, and TotalAmount. Which SQL statement will delete all orders placed before January 1, 2023?

hard

A.DROP FROM Orders WHERE OrderDate < '2023-01-01'
✓ B.DELETE FROM Orders WHERE OrderDate < '2023-01-01'
C.DELETE OrderDate < '2023-01-01' FROM Orders
D.REMOVE FROM Orders WHERE OrderDate < '2023-01-01'

Why B: DELETE FROM Orders WHERE OrderDate < '2023-01-01' is the correct SQL syntax for removing rows that match a condition. DELETE is the standard DML statement for row-level removal, and the WHERE clause filters which orders are affected. The date literal '2023-01-01' is compared against OrderDate to remove all orders placed before that date.

Written by Johnson Ajibi, MSc IT Security

Senior Network & Security Engineer · founder of Courseiva

This FC0-U71 practice question is part of Courseiva's free CompTIA 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 FC0-U71 exam.

FC0-U71 Practice Question: Refer to the exhibit

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question

3 more ways this is tested on FC0-U71