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.

Deze oefening maakt deel uit van de cursus

Introduction to Relational Databases in SQL

Cursus bekijken

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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

Code bewerken en uitvoeren