Views with CHECK OPTION in PostgreSQL - Mini Project: Build & Apply

Creating and Using Views with CHECK OPTION in PostgreSQL

📖 Scenario: You are managing a small company's employee database. You want to create a special view that shows only employees from the 'Sales' department. You also want to make sure that any changes made through this view keep employees in the 'Sales' department.

🎯 Goal: Create a PostgreSQL view named sales_employees that shows only employees from the 'Sales' department. Use the WITH CHECK OPTION to ensure that any inserts or updates through this view keep the employee in the 'Sales' department.

📋 What You'll Learn

Create a table named employees with columns id, name, and department.

Insert at least three employees with different departments including 'Sales'.

Create a view named sales_employees that selects only employees from the 'Sales' department.

Add WITH CHECK OPTION to the view to enforce department constraint on inserts and updates.

💡 Why This Matters

🌍 Real World

Views with CHECK OPTION are used in real companies to create safe, filtered access to data. For example, department managers can see and modify only their employees.

💼 Career

Database developers and administrators use views with CHECK OPTION to enforce business rules and data security without changing application code.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), and department (text). Insert these exact rows: (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Sales').

PostgreSQL

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Create a view for Sales department employees

Create a view named sales_employees that selects all columns from employees where department = 'Sales'.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Sales');

-- Create the sales_employees view
-- Your code here

Need a hint?

Use CREATE VIEW and a SELECT statement with a WHERE clause.

3

Add WITH CHECK OPTION to the view

Modify the sales_employees view to include WITH CHECK OPTION at the end to ensure only employees in the 'Sales' department can be inserted or updated through this view.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Sales');

-- Modify the sales_employees view to add WITH CHECK OPTION
-- Your code here

Need a hint?

Add WITH CHECK OPTION at the end of the CREATE VIEW statement.

4

Verify the view enforces the department constraint

Write an INSERT statement that tries to add an employee with department = 'HR' through the sales_employees view. This should fail because of the WITH CHECK OPTION. Then write a correct INSERT statement through the view with department = 'Sales'.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Sales');

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

-- Try inserting an employee with department 'HR' through the view (should fail)
-- Then insert an employee with department 'Sales' through the view (should succeed)
-- Your code here

Need a hint?

Try inserting a row with department = 'HR' through the view, then insert a row with department = 'Sales'.