Question

What foreign key action should be added to ensure that if a supplier is removed from the Supplier table, the products associated with the same supplier are also removed? (Ch.4)

Answer 1

To ensure the deletion of associated products when a supplier is removed, the CASCADE DELETE action should be set on the foreign key constraint within the Products table.

Step-by-step explanation:

The foreign key action that should be added to ensure that if a supplier is removed from the Supplier table, the products associated with the same supplier are also removed, is the CASCADE DELETE action. When you set a foreign key constraint with CASCADE DELETE, it means that if a record in the parent table (in this case, the Supplier table) is deleted, then the corresponding records in the child table (here, the Products table) will be automatically deleted as well.

This maintains referential integrity by making sure there are no orphaned records in the child table. In SQL, this is accomplished by adding ON DELETE CASCADE to the foreign key constraint definition when creating or altering the child table.