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.

Latihan ini adalah bagian dari kursus

Introduction to Relational Databases in SQL

Lihat Kursus

Latihan interaktif praktis

Cobalah latihan ini dengan menyelesaikan kode contoh berikut.

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

Edit dan Jalankan Kode