Practice Questions: Constraints and Integrity

Practice Questions for constraints and integrity in your SQL database resilience

Michael D. Reilly

November 1, 1999

2 Min Read
ITPro Today logo

Q1. 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

  2. The syntax you would use is
    ALTER TABLE
    ADD CONSTRAINT pk_name
    NOCHECK
    ON

  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.

Q2. 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 (table listing 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 umbers 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.

Related:Kubernetes 1.32 'Penelope' Introduces Key Innovations for Open Source Cloud Deployment

Q3. You've defined a primary-key constraint on a table. You want to rebuild the index with 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 using the DROP EXISTING option and specify the correct fill factor.

  4. You can't apply a fill factor to indexes built automatically by constraints.

Q4. 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.

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.

You May Also Like