Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Answers to December Practice Questions
Answers to December practice questions on constraints and integrity.
Michael D. Reilly
December 3, 1999
2 Min Read
You want to create a primary-key constraint on a table with existing data, but you don't need to check the existing data because you know the primary-key column contains only unique values. Select the correct statement:
The syntax you would use is:
ALTER TABLE WITH NOCHECKADD CONSTRAINT pk_nameONThe syntax you would use is:
ALTER TABLE ADD CONSTRAINT pk_name NOCHECK ONYou can't add a primary-key constraint to a table with existing data.
You can add the constraint, but you can't turn off the check of the existing data.
The correct answer is D. Answer A looks plausible but doesn't work. B mixes the syntax for adding a constraint with the syntax for disabling an existing constraint, but you can't do either with a primary-key constraint. And C is definitely not true.
You want to place a constraint on a column to check that the value entered is among the part numbers your company manufactures. The best approach is:
Place a Check constraint on the column to make sure the value entered is IN (list of part numbers).
Place a Check constraint on the column to make sure that the value entered is IN (SELECT part_no FROM parts_list) where parts_list is a table listing the part numbers and descriptions.
Build a foreign-key reference from this column to the part_no column in the parts_list table, where parts_list is a table listing the part numbers and descriptions.
Build a foreign-key reference to this column from the part_no column in the parts_list table, where parts_list is a table listing the part numbers and descriptions.
C is the right answer. A is too much work and the list might be large. B is wrong because you can't use a subquery in a constraint. D builds a relationship, but in the wrong direction.
You've defined a primary-key constraint on a table. You want to rebuild the index and use a fill factor of 80 percent. Select all correct statements:
You can't drop and rebuild the index because the constraint automatically builds it; therefore, you can't change the fill factor.
You can drop and recreate the constraint, which lets you specify a fill factor for the index.
You can rebuild the index by using the DROP EXISTING option, then specify the correct fill factor.
You can't apply a fill factor to indexes built automatically by constraints.
C is the best correct answer, although B will also work. A and D are not correct statements.
You have both a trigger and a foreign-key referential integrity check on a table to prevent deletes. Select the correct statement:
When you try to delete a record, the constraint activates first, followed by the trigger.
When you try to delete a record, the constraint activates, but the trigger doesn't.
When you try to delete a record, the trigger overrides the constraint.
You can't have both a trigger and a constraint enforcing the same referential integrity.
B is correct. The constraint prevents the delete operation from happening, so the trigger will never fire.
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
