You are a database developer on an instance of SQL Server 2008. You have a Product table that contains a column-level CHECK constraint named qty_check, which verifies that the QuantityOnHand value is greater than or equal to zero.
A stored procedure runs each night to gather information from warehouse locations and update the QuantityOnHand column values. You have some reports that use historical data and include information for items that have been discontinued. For these discontinued items, you want to insert rows into the Product table that have a negative QuantityOnHand.
You want the stored procedure that runs each night to be unaffected. Which action should you take?
A. Disable the constraint and re-enable it after inserting the rows.
B. Drop the constraint, insert the rows, and re-create the constraint.
C. Drop the constraint and define the validation for the constraint in a DML trigger.
D. Re-create the constraint as a table-level constraint.
Correct Answer: A
Explanation/Reference:
You should disable the constraint and re-enable it after inserting the rows containing negative QuantityOnHand values. You can temporarily disable a FOREIGN KEY or CHECK constraint by including the NOCHECK CONSTRAINT clause in an ALTER TABLE statement. When a constraint is disabled, the constraint condition is not checked when data is inserted or updated. For example, in this scenario, you could use the following statement to disable the qty_check constraint:
ALTER TABLE Product NOCHECK CONSTRAINT qty_check;
After disabling the constraint, you could insert rows into the table that violate the constraint because the constraint will not be checked. Finally, you could use the following statement to re-enable the constraint:
ALTER TABLE Product CHECK CONSTRAINT qty_check;
You should not re-create the constraint as a table-level constraint. Whether a constraint is defined at the table level or the column level does not affect how validation is performed. All constraints defined at either level are enforced if they are enabled.
You should not drop the constraint and define the validation for the constraint in a DML trigger. Although you can perform complex validation within a DML trigger, this requires more development effort. In addition, the validation would still occur that would prevent you from inserting the rows. In this scenario, you should temporarily disable the CHECK constraint, insert the row, and then re-enable the CHECK constraint.
You should not drop the constraint, insert the rows, and re-create the constraint because this would require more effort. Instead, you should temporarily disable the constraint.
