Home » Microsoft » MB6-886 » What should you do?
You are a database developer on an instance of SQL Server 2008. You have a WorkOrder table and a WorkOrderDetail table. The WorkOrder table contains one row for each work order, and the WorkOrderDetail table contains line items of work performed for each work order.
You want to create a FOREIGN KEY constraint to relate the two tables. You want to ensure that if a work order identifier in the WorkOrder table is updated, that the corresponding rows in the WorkOrderDetail table are also updated to maintain referential integrity.
What should you do?
A. Use a trigger to enforce referential integrity.
B. Create a CHECK constraint on the WorkOrder table.
C. Include the ON UPDATE CASCADE clause in your FOREIGN KEY constraint.
D. Include the WITH CHECK clause in your FOREIGN KEY constraint.
Correct Answer: C
Explanation/Reference:
When you include the ON UPDATE CASCADE clause, it ensures that if a referenced row in the parent table is updated, then the corresponding child rows are updated and referential integrity is maintained. This would ensure that if a user updated the identifier of a WorkOrder, the corresponding rows in the WorkOrderDetail table would also be updated.
You should not use a trigger to enforce referential integrity. Triggers should not be used when constraints can accomplish the same task. FOREIGN KEY constraints are used to enforce referential integrity.
You should not create a CHECK constraint on the WorkOrder table. A CHECK constraint is used to restrict the data allowed for a column to specific values. A CHECK constraint consists of a Boolean expression that evaluates to either TRUE or FALSE. If the expression evaluates to TRUE, the value is allowed for the column, and if the expression evaluates to FALSE, the value is not allowed for the column. CHECK constraints can be defined at the table level or column level, but only CHECK constraints defined at the table level can use columns other than the constrained column in the constraint expression.
You should not include the WITH CHECK clause in your FOREIGN KEY constraint because this only enables the constraint. By default, FOREIGN KEY and CHECK constraints are enabled when they are created. You can use the NOCHECK CONSTRAINT clause of the ALTER TABLE statement to temporarily disable a FOREIGN KEY or CHECK constraint if you need to insert rows that violate the constraint.