70-467 Question #133: Real Exam Question with Answer & Explanation

The correct answer is D: a CHECK constraint. A CHECK constraint can reference a user-defined function to validate each row before it is inserted, making it the best declarative option for pre-insertion validation in SQL Server.

Question

You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table. What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

Options

Aa FOREIGN KEY constraint
Ba data manipulation language (DML) trigger
Ca DEFAULT constraint
Da CHECK constraint
Ea Data Definition Language (DDL) trigger

Explanation

A CHECK constraint can reference a user-defined function to validate each row before it is inserted, making it the best declarative option for pre-insertion validation in SQL Server.

Common mistakes.

A. A FOREIGN KEY constraint enforces referential integrity between columns in related tables and cannot invoke a user-defined function to perform custom row validation logic.
B. A DML trigger fires in response to INSERT, UPDATE, or DELETE events but does so within the same transaction after the row operation begins, making a CHECK constraint more precise and direct for pre-insertion validation - the question asks for the BEST answer.
C. A DEFAULT constraint supplies a predefined value for a column when none is provided and does not execute any function to validate the content of an incoming row.
E. A DDL trigger fires in response to schema-change events such as CREATE, ALTER, or DROP statements, not in response to data-manipulation operations like INSERT on the Orders table.

Concept tested. CHECK constraint invoking user-defined function for row validation

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-check-constraints

Community Discussion

No community discussion yet for this question.

Full 70-467 Practice