Exercise

Change the referential integrity behavior of a key

So far, you implemented three foreign key constraints:

  1. professors.university_id to universities.id
  2. affiliations.organization_id to organizations.id
  3. affiliations.professor_id to professors.id

These foreign keys currently have the behavior ON DELETE NO ACTION. Here, you're going to change that behavior for the column referencing organizations from affiliations. If an organization is deleted, all its affiliations (by any professor) should also be deleted.

Altering a key constraint doesn't work with ALTER COLUMN. Instead, you have to DROP the key constraint and then ADD a new one with a different ON DELETE behavior.

For deleting constraints, though, you need to know their name. This information is also stored in information_schema.

Instructions 1/4

undefined XP
    1
    2
    3
    4

Have a look at the existing foreign key constraints by querying table_constraints in information_schema.