Change the referential integrity behavior of a key

So far, you implemented three foreign key constraints:

professors.university_id to universities.id
affiliations.organization_id to organizations.id
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.

This exercise is part of the course

Introduction to Relational Databases in SQL

View Course

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Identify the correct constraint name
SELECT constraint_name, table_name, constraint_type
FROM information_schema.___
WHERE constraint_type = 'FOREIGN KEY';

Edit and Run Code