Change the referential integrity behavior of a key
So far, you implemented three foreign key constraints:
professors.university_id
touniversities.id
affiliations.organization_id
toorganizations.id
affiliations.professor_id
toprofessors.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
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';