Views with CHECK OPTION in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of INSERT on a view with CHECK OPTION

Consider the following table and view in PostgreSQL:

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, department TEXT);
INSERT INTO employees (name, department) VALUES ('Alice', 'Sales'), ('Bob', 'HR');

CREATE VIEW sales_employees AS
  SELECT * FROM employees WHERE department = 'Sales'
  WITH CHECK OPTION;

What happens when you run this query?

INSERT INTO sales_employees (name, department) VALUES ('Charlie', 'HR');

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, department TEXT);
INSERT INTO employees (name, department) VALUES ('Alice', 'Sales'), ('Bob', 'HR');

CREATE VIEW sales_employees AS
  SELECT * FROM employees WHERE department = 'Sales'
  WITH CHECK OPTION;

INSERT INTO sales_employees (name, department) VALUES ('Charlie', 'HR');

A The insert succeeds and adds Charlie to employees with department 'HR'.

B The insert succeeds but Charlie's department is automatically changed to 'Sales'.

C The insert succeeds but Charlie is not visible in the sales_employees view.

D The insert fails with a CHECK OPTION violation error.

💡 Hint

Think about what the CHECK OPTION enforces on inserts through the view.

The WITH CHECK OPTION on the view ensures that any inserted or updated rows through the view must satisfy the view's WHERE condition. Since 'Charlie' has department 'HR', which does not match 'Sales', the insert violates the CHECK OPTION and fails.

❓ query_result

intermediate

Update behavior on a view with CHECK OPTION

Given the same employees table and sales_employees view as before, what is the result of this update?

UPDATE sales_employees SET department = 'HR' WHERE name = 'Alice';

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, department TEXT);
INSERT INTO employees (name, department) VALUES ('Alice', 'Sales'), ('Bob', 'HR');

CREATE VIEW sales_employees AS
  SELECT * FROM employees WHERE department = 'Sales'
  WITH CHECK OPTION;

UPDATE sales_employees SET department = 'HR' WHERE name = 'Alice';

A The update fails with a CHECK OPTION violation error.

B The update succeeds and Alice's department changes to 'HR'.

C The update succeeds but Alice remains visible in the view with department 'Sales'.

D The update succeeds but Alice is deleted from the employees table.

💡 Hint

Consider what the CHECK OPTION enforces on updates through the view.

The WITH CHECK OPTION requires that updated rows still satisfy the view's WHERE condition. Changing Alice's department to 'HR' would make her row no longer satisfy the condition, so the update fails.

🧠 Conceptual

advanced

Purpose of WITH CHECK OPTION in views

What is the main purpose of using WITH CHECK OPTION when creating a view in PostgreSQL?

A To allow the view to be updated even if it contains aggregate functions.

B To automatically create indexes on the view for faster queries.

C To ensure that any data inserted or updated through the view satisfies the view's defining condition.

D To restrict SELECT queries on the view to only certain users.

💡 Hint

Think about data integrity when modifying data through views.

WITH CHECK OPTION enforces that any insert or update through the view must produce rows that satisfy the view's WHERE clause, preserving data consistency.

📝 Syntax

advanced

Identify the syntax error in view creation

Which of the following CREATE VIEW statements will cause a syntax error in PostgreSQL?

A CREATE VIEW v AS SELECT * FROM employees WHERE department = 'Sales' CHECK OPTION;

B CREATE VIEW v AS SELECT * FROM employees WHERE department = 'Sales' WITH LOCAL CHECK OPTION;

C CREATE VIEW v AS SELECT * FROM employees WHERE department = 'Sales' WITH CASCADED CHECK OPTION;

D CREATE VIEW v AS SELECT * FROM employees WHERE department = 'Sales' WITH CHECK OPTION;

💡 Hint

Check the correct syntax for adding CHECK OPTION to a view.

The correct syntax requires the keyword WITH before CHECK OPTION. Option A omits WITH, causing a syntax error.

🔧 Debug

expert

Diagnose why an update through a view with CHECK OPTION fails

You have this setup:

CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);
INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Notebook', 3.0);

CREATE VIEW expensive_products AS
  SELECT * FROM products WHERE price > 2
  WITH CHECK OPTION;

When you run this update, it fails:

UPDATE expensive_products SET price = 1.0 WHERE name = 'Notebook';

Why does this update fail?

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);
INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Notebook', 3.0);

CREATE VIEW expensive_products AS
  SELECT * FROM products WHERE price > 2
  WITH CHECK OPTION;

UPDATE expensive_products SET price = 1.0 WHERE name = 'Notebook';

A Because the view does not allow updates on the price column.

B Because the updated price 1.0 does not satisfy the view's WHERE condition price > 2, violating the CHECK OPTION.

C Because the products table has a trigger preventing price changes below 2.

D Because the UPDATE statement syntax is incorrect for views with CHECK OPTION.

💡 Hint

Consider the view's filter condition and how CHECK OPTION enforces it on updates.

The WITH CHECK OPTION enforces that updated rows must still satisfy the view's WHERE clause. Setting price to 1.0 breaks the condition price > 2, so the update fails.