Updatable Views & WITH CHECK OPTION0%

Updatable Views & WITH CHECK OPTION

Intermediate13 min read•Updated: 2026-09-12

Updatable Views & WITH CHECK OPTION

While views are predominantly used for data retrieval, MySQL allows certain views to be updatable. An updatable view permits INSERT, UPDATE, and DELETE statements executed against the view to be directly translated to modifications on the underlying base table.

To prevent updates or inserts that violate the view's qualifying filtering criteria, MySQL provides the WITH CHECK OPTION constraint.

Rules for Updatable Views in MySQL

For a view to be updatable, there must be a direct 1-to-1 relationship between rows in the view and rows in the base table. A view is NOT updatable if its definition contains any of the following:

Aggregate functions (SUM(), AVG(), COUNT(), MIN(), MAX())
DISTINCT
GROUP BY or HAVING clauses
UNION or UNION ALL
Subqueries in the SELECT list
Non-updatable views in the FROM clause
Join views where updates span multiple base tables simultaneously in ambiguous ways

Example of an Updatable View

1-- Base table: employees
2CREATE TABLE employees (
3    emp_id INT PRIMARY KEY AUTO_INCREMENT,
4    first_name VARCHAR(50) NOT NULL,
5    last_name VARCHAR(50) NOT NULL,
6    department VARCHAR(50) NOT NULL,
7    salary DECIMAL(10, 2) NOT NULL,
8    status ENUM('active', 'resigned', 'terminated') DEFAULT 'active'
9);
10 
11-- Updatable View: active engineering staff
12CREATE OR REPLACE VIEW v_engineering_staff AS
13SELECT emp_id, first_name, last_name, department, salary
14FROM employees
15WHERE department = 'Engineering' AND status = 'active';

Now, issuing an update directly through the view:

1UPDATE v_engineering_staff
2SET salary = salary * 1.08
3WHERE emp_id = 101;

This successfully modifies the salary column in the underlying employees table for employee 101!

The Risk Without WITH CHECK OPTION

Consider what happens if an update changes the filtering column:

1UPDATE v_engineering_staff
2SET department = 'Marketing'
3WHERE emp_id = 101;

Without protection, MySQL executes this update on employees. However, employee 101 is now in 'Marketing', meaning they instantly "vanish" from v_engineering_staff! Even worse, an INSERT could insert a record with department = 'Finance' through v_engineering_staff.

Enforcing WITH CHECK OPTION

By appending WITH CHECK OPTION, MySQL validates any inserted or updated row against the view's WHERE condition:

1CREATE OR REPLACE VIEW v_engineering_staff AS
2SELECT emp_id, first_name, last_name, department, salary
3FROM employees
4WHERE department = 'Engineering' AND status = 'active'
5WITH CHECK OPTION;

Now, if a query attempts to change the department out of 'Engineering':

1-- This will fail with an error!
2UPDATE v_engineering_staff
3SET department = 'Marketing'
4WHERE emp_id = 101;
5-- ERROR 1369 (HY000): CHECK OPTION failed 'company_db.v_engineering_staff'

MySQL provides two scopes for WITH CHECK OPTION:

1
WITH CASCADED CHECK OPTION (Default): Evaluates rules for the current view and all underlying nested views.
2
WITH LOCAL CHECK OPTION: Evaluates rules only for the current view definition.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

When is a MySQL view considered updatable?

Question 2

Which clause prevents INSERT or UPDATE statements through a view from violating the view's WHERE condition?

Question 3

What happens when WITH CHECK OPTION is violated during an UPDATE?

Question 4

What is the difference between LOCAL and CASCADED check options?

Question 5

Which of the following view definitions is fundamentally NOT updatable?

Next Lesson

Managing & Dropping Views

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating & Querying Views (CREATE VIEW)	Managing & Dropping Views

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Managing & Dropping Views

Updatable Views & WITH CHECK OPTION

Intermediate13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Updatable Views & WITH CHECK OPTION

To prevent updates or inserts that violate the view's qualifying filtering criteria, MySQL provides the WITH CHECK OPTION constraint.

Rules for Updatable Views in MySQL

Aggregate functions (SUM(), AVG(), COUNT(), MIN(), MAX())
DISTINCT
GROUP BY or HAVING clauses
UNION or UNION ALL
Subqueries in the SELECT list
Non-updatable views in the FROM clause
Join views where updates span multiple base tables simultaneously in ambiguous ways

Example of an Updatable View

1-- Base table: employees
2CREATE TABLE employees (
3    emp_id INT PRIMARY KEY AUTO_INCREMENT,
4    first_name VARCHAR(50) NOT NULL,
5    last_name VARCHAR(50) NOT NULL,
6    department VARCHAR(50) NOT NULL,
7    salary DECIMAL(10, 2) NOT NULL,
8    status ENUM('active', 'resigned', 'terminated') DEFAULT 'active'
9);
10 
11-- Updatable View: active engineering staff
12CREATE OR REPLACE VIEW v_engineering_staff AS
13SELECT emp_id, first_name, last_name, department, salary
14FROM employees
15WHERE department = 'Engineering' AND status = 'active';

Now, issuing an update directly through the view:

1UPDATE v_engineering_staff
2SET salary = salary * 1.08
3WHERE emp_id = 101;

This successfully modifies the salary column in the underlying employees table for employee 101!

The Risk Without WITH CHECK OPTION

Consider what happens if an update changes the filtering column:

1UPDATE v_engineering_staff
2SET department = 'Marketing'
3WHERE emp_id = 101;

Enforcing WITH CHECK OPTION

By appending WITH CHECK OPTION, MySQL validates any inserted or updated row against the view's WHERE condition:

1CREATE OR REPLACE VIEW v_engineering_staff AS
2SELECT emp_id, first_name, last_name, department, salary
3FROM employees
4WHERE department = 'Engineering' AND status = 'active'
5WITH CHECK OPTION;

Now, if a query attempts to change the department out of 'Engineering':

1-- This will fail with an error!
2UPDATE v_engineering_staff
3SET department = 'Marketing'
4WHERE emp_id = 101;
5-- ERROR 1369 (HY000): CHECK OPTION failed 'company_db.v_engineering_staff'

MySQL provides two scopes for WITH CHECK OPTION:

1
WITH CASCADED CHECK OPTION (Default): Evaluates rules for the current view and all underlying nested views.
2
WITH LOCAL CHECK OPTION: Evaluates rules only for the current view definition.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

When is a MySQL view considered updatable?

Question 2

Which clause prevents INSERT or UPDATE statements through a view from violating the view's WHERE condition?

Question 3

What happens when WITH CHECK OPTION is violated during an UPDATE?

Question 4

What is the difference between LOCAL and CASCADED check options?

Question 5

Which of the following view definitions is fundamentally NOT updatable?

Next Lesson

Managing & Dropping Views

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating & Querying Views (CREATE VIEW)	Managing & Dropping Views

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next