Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 40 ยท Stored Programs and Triggers

Views and Updatable Views in MariaDB

Create, replace and drop MariaDB views, learn which views are updatable, use WITH CHECK OPTION, ALGORITHM and SQL SECURITY, and see when a view helps or hurts.

A view is a saved SELECT with a name. You query it like a table, but it stores no rows of its own: MariaDB runs the saved query each time. Views give you three things: a shorter way to repeat a complex join, a stable interface that hides the real table layout, and a security layer that exposes only some columns or rows to a user.

This lesson shows how to create and manage views, when you can INSERT/UPDATE/DELETE through one, and how to protect data with WITH CHECK OPTION and SQL SECURITY. Stored programs in the following lessons build on these ideas.

Creating a view

SQL
CREATE VIEW employee_directory AS
SELECT e.id,
       CONCAT(e.first_name, ' ', e.last_name) AS full_name,
       e.title,
       d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id;

Then use it like any table:

SQL
SELECT * FROM employee_directory WHERE department = 'Sales' ORDER BY full_name;

Useful variations:

SQL
CREATE OR REPLACE VIEW employee_directory AS ...;   -- redefine without dropping
CREATE VIEW IF NOT EXISTS employee_directory AS ...;
DROP VIEW IF EXISTS employee_directory;
SHOW CREATE VIEW employee_directory;                -- see the stored definition
SHOW FULL TABLES WHERE Table_type = 'VIEW';         -- list views

You can name the columns explicitly: CREATE VIEW v (a, b) AS SELECT x, y FROM t;. Views can reference other views, but deep stacks of views make plans hard to read, so keep nesting shallow.

SQL Create a reporting view ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The view hides the join and the aggregation. When the definition changes (a new column, a corrected formula), you fix it in one place and every report that uses the view benefits.

How MariaDB executes a view: ALGORITHM

MariaDB chooses between two strategies:

  • MERGE: the view's SELECT is merged into the outer query, as if you had typed it inline. This is cheap, allows index use, and is required for updatable views.
  • TEMPTABLE: the view is materialized into a temporary table first, then the outer query reads it. Used when the definition has DISTINCT, GROUP BY, aggregates, UNION, LIMIT, and so on.
SQL
CREATE ALGORITHM = MERGE VIEW v AS ...;      -- error if merging is impossible
CREATE ALGORITHM = TEMPTABLE VIEW v AS ...;
CREATE ALGORITHM = UNDEFINED VIEW v AS ...;  -- default: let the server choose

A view over a GROUP BY therefore materializes every time, so a filter such as WHERE department = 'Sales' on the outer query may be applied only after the whole aggregation ran. Check with EXPLAIN (explain and index design).

Updatable views

A view is updatable when each row of the view maps to exactly one row of one base table. MariaDB then lets you run UPDATE, DELETE and usually INSERT against it. A view stops being updatable when its definition contains:

  • aggregate functions or GROUP BY / HAVING;
  • DISTINCT, UNION, LIMIT (partly), or a subquery in the select list;
  • ALGORITHM = TEMPTABLE.

A join view can be partly updatable: you may change columns of one base table at a time. For INSERT through a view, every NOT NULL column without a default in the base table must be present in the view.

SQL
CREATE VIEW active_customers AS
SELECT id, name, email, status
FROM customers
WHERE status = 'active';

UPDATE active_customers SET email = '[email protected]' WHERE id = 7;   -- changes customers
INSERT INTO active_customers (name, email, status) VALUES ('Zoe', '[email protected]', 'inactive');

The second statement succeeds, but the new row does not appear in the view, because it does not satisfy status = 'active'. That surprise is what WITH CHECK OPTION prevents.

WITH CHECK OPTION

SQL
CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, status
FROM customers
WHERE status = 'active'
WITH CHECK OPTION;

Now any INSERT or UPDATE through the view that would produce a row the view cannot show is rejected with an error. Use WITH CASCADED CHECK OPTION (the default) to also check the conditions of underlying views, or WITH LOCAL CHECK OPTION to check only this view's own condition.

The portable engine has no updatable-view support; it lets you update the base table, then read through the view. A runnable stand-in for the filter behaviour:

SQL A view always shows the current data

Press Run (or Ctrl + Enter) to see the result here.

The view has no copy of the data: after the base table changed, the second count includes Bo and still excludes Di.

Security: SQL SECURITY and column hiding

Views are a simple way to expose a subset of data. Grant users access to the view and not to the table:

SQL
CREATE VIEW staff_public AS
SELECT id, first_name, last_name, title, department_id FROM employees;   -- no salary

GRANT SELECT ON hr.staff_public TO 'reporting'@'%';

SQL SECURITY decides whose privileges are used when the view runs:

SQL
CREATE DEFINER = 'app_admin'@'localhost' SQL SECURITY DEFINER VIEW staff_public AS ...;
CREATE SQL SECURITY INVOKER VIEW staff_public2 AS ...;

With DEFINER (the default) the view runs with the privileges of the account that created it, so a low-privilege user can read the data the definer is allowed to see. With INVOKER it uses the caller's rights. DEFINER views are the usual choice for the column-hiding pattern, but a definer account that is later dropped leaves the view broken, so use a dedicated, stable account (see users, grants and roles).

Views and performance

  • A merged view costs nothing extra; a materialized (TEMPTABLE) view can be slow on large tables.
  • A view cannot have its own index. The base tables' indexes are used when the view is merged.
  • SELECT * in a view definition is expanded at creation time. Adding a column to the table later does not appear in the view until you recreate it.
  • If you really need stored results, use a summary table refreshed by an event (MariaDB has no built-in materialized views).

Common mistakes

  • Assuming a view stores data or speeds a query up by itself.
  • Running INSERT through a filtered view without WITH CHECK OPTION and "losing" rows.
  • Building views of views of views and then wondering about the plan.
  • Forgetting that dropping or renaming a base table breaks the view; CHECK TABLE view_name finds that.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a view store?

    A view is a saved query. MariaDB has no built-in materialized views.
  2. 2. Which view definition is NOT updatable?

    A grouped view has no one-to-one mapping from view rows to base rows.
  3. 3. What does WITH CHECK OPTION do?

    It keeps data changes consistent with the view's WHERE condition.
  4. 4. With SQL SECURITY DEFINER, whose privileges apply when the view is queried?

    That is why DEFINER views can expose a subset of a table to users who cannot read the table.

Summary

  • CREATE [OR REPLACE] VIEW name AS SELECT ... saves a query; DROP VIEW removes it; nothing is stored.
  • MERGE views are cheap and updatable; TEMPTABLE views materialize on every use.
  • WITH CHECK OPTION stops changes that would fall outside the view's filter.
  • Views plus GRANT hide columns and rows; choose DEFINER or INVOKER on purpose.

Next: Variables and User-Defined Functions