Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Working with Views

Deleting views
Deleting and modifying tables with views
8. Modifying tables with dependent views – 2
Modifying views
Summary

Instruction

So, what happens if we try to alter a table in a way that affects a dependent view? We have the same table and view:

CREATE TABLE gym (
  id integer PRIMARY KEY,
  name varchar(32),
  city varchar(32),
  monthly_fee decimal(5, 2),
  max_capacity integer
);
CREATE VIEW big_gyms_london AS
SELECT id, name, max_capacity
FROM gym
WHERE city = 'London'
  AND max_capacity > 50;

We now want to delete the max_capacity column, but we know that the big_gyms_london view uses it. As you may expect, the standard DROP COLUMN syntax won't work – we need to add the CASCADE modifier:

ALTER TABLE gym
DROP COLUMN max_capacity CASCADE;

Note that the instruction above will delete the max_capacity column AND all views dependent on it.

Exercise

Again, you're given the course table and a view dependent on it:

CREATE TABLE course (
  id integer PRIMARY KEY,
  name varchar(128),
  lecturer varchar(128),
  ects_points integer,
  max_students integer,
  has_exam boolean
);
CREATE VIEW courses_prof_smith AS
SELECT id, name, max_students, has_exam
FROM course
WHERE lecturer = 'John Smith';

Your task is to get rid of the max_students column from the course table.