Answers to December Practice Questions

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:

1. The syntax you would use is:

   ALTER TABLE WITH NOCHECK
   ADD CONSTRAINT pk_name
   ON <column_name>

2. The syntax you would use is:

   ALTER TABLE 
   ADD CONSTRAINT pk_name
         NOCHECK 
   ON <column_name>  
   	  

3. You can't add a primary-key constraint to a table with existing data.
   
   
4. 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:

1. Place a Check constraint on the column to make sure the value entered is IN (list of part numbers).
   
   
2. 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.
   
   
3. 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.
   
   
4. 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:

1. You can't drop and rebuild the index because the constraint automatically builds it; therefore, you can't change the fill factor.
   
   
2. You can drop and recreate the constraint, which lets you specify a fill factor for the index.
   
   
3. You can rebuild the index by using the DROP EXISTING option, then specify the correct fill factor.
   
   
4. 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:

1. When you try to delete a record, the constraint activates first, followed by the trigger.
   
   
2. When you try to delete a record, the constraint activates, but the trigger doesn't.
   
   
3. When you try to delete a record, the trigger overrides the constraint.
   
   
4. 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.