# Mariadb Create View

> Explains MariaDB-specific view syntax and behavior, including CREATE OR REPLACE VIEW, ALGORITHM options, SQL SECURITY, updatability rules, and WITH CHECK OPTION, for writing or reviewing MariaDB view statements.

- Skill: `mariadb-corporation/mariadb-create-view` (Agent Skill)
- Install (CLI): `npx skillmds add mariadb-corporation/mariadb-create-view`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mariadb-corporation/mariadb-create-view/raw
- Safety review: PASS (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics, SQL & Databases
- Tags: Alter View, Create View, Database, Drop View, Mariadb, Sql, Views
- Author: mariadb-corporation (https://skillmd.com/u/mariadb-corporation)
- Updated: 2026-08-22
- Page: https://skillmd.com/skills/mariadb-corporation/mariadb-create-view

---


# CREATE VIEW in MariaDB

*Last updated: 2026-06-24*

This skill covers the **delta between standard SQL views and MariaDB's** — the view-definition options, updatability rules, and security semantics that trip up generated SQL. It assumes the agent already knows the basic `CREATE VIEW v AS SELECT …` form. The view body is a `SELECT`; for its clauses see `mariadb-select`.

> **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.

## What LLMs Often Miss

| If the agent writes / assumes… | …prefer the MariaDB form |
|---|---|
| `DROP VIEW x; CREATE VIEW x …` to redefine a view | `CREATE OR REPLACE VIEW x AS …` — atomic, no window where the view is missing. (`ALTER VIEW x AS …` also works when it already exists) |
| `CREATE OR REPLACE VIEW IF NOT EXISTS …`, assuming the two combine | Hard error (`ER_WRONG_USAGE`). `OR REPLACE` and `IF NOT EXISTS` are mutually exclusive — pick one |
| Omits `SQL SECURITY`, expecting the caller's privileges to apply | The default is **`SQL SECURITY DEFINER`** — the view executes with the *definer's* privileges, not the invoker's. For caller-privilege semantics you must write `SQL SECURITY INVOKER` explicitly |
| Treats any view as updatable / `INSERT`-able | A view is **not** updatable if its `SELECT` uses an aggregate, `DISTINCT`, `GROUP BY`, `HAVING`, `UNION`, a subquery in the select list, an outer join, references no base table, or refers to one base-table column more than once. `ALGORITHM = TEMPTABLE` is never updatable |
| Sets `ALGORITHM = TEMPTABLE` on a view it then writes through | `TEMPTABLE` views are never updatable; only `MERGE` (or an `UNDEFINED` view the optimizer can merge) can be written through |
| `INSERT`s through a view whose columns are expressions (`col + 1`, `LOWER(col)`, literals) | Insertable views need every column to be a plain base-table column, must include all `NOT NULL`/no-default base columns, and may not repeat a base column. Otherwise `ERROR 1348: Column is not updatable` |
| Bare `WITH CHECK OPTION` on a view built on another view, expecting only the local predicate to be checked | Bare `WITH CHECK OPTION` means **`CASCADED`** (checks this view *and* all underlying views). Use `WITH LOCAL CHECK OPTION` to check only this view's predicate |
| Puts `ORDER BY` inside the view to guarantee output order | The view's `ORDER BY` is **ignored** whenever the outer query has its own `ORDER BY`. Don't rely on in-view ordering — sort in the query that reads the view |
| Expects `SELECT *` in a view to pick up columns added to the base table later | The definition is **frozen at creation**: `SELECT *` is expanded to the columns that existed then. New base-table columns don't appear until you recreate the view |
| Tries to index a view, or `CREATE TEMPORARY VIEW`, or put a subquery in the view's `FROM` | None are allowed. Index the underlying base table; there is no temporary view; a view's `SELECT` can't have a derived table in `FROM` |
| Leaves expression columns unnamed | Every view column needs a unique name — give expressions an alias, or supply an explicit `(column_list)` whose length matches the select list |

## Syntax

```sql
CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW [IF NOT EXISTS] view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION];
```

Clause order is fixed (`OR REPLACE` → `ALGORITHM` → `DEFINER` → `SQL SECURITY` → `VIEW`). `DEFINER` accepts a role or `CURRENT_ROLE`, not just a user. `CREATE VIEW` is atomic DDL.

## `ALGORITHM`

| Algorithm | Behavior |
|---|---|
| `MERGE` | The view text is merged into the referencing query. More efficient, and **required for an updatable view**. Not possible if the view uses `GROUP BY`, `HAVING`, `LIMIT`, `DISTINCT`, `UNION`, an aggregate, or a select-list subquery |
| `TEMPTABLE` | The view result is materialized into a temporary table. Never updatable; can release underlying locks earlier |
| `UNDEFINED` (default) | The server chooses, preferring `MERGE` and falling back to a temp table when the shape forbids merging |

## Updatable views and `WITH CHECK OPTION`

When a view is updatable, `WITH CHECK OPTION` rejects writes that would produce rows the view can't see:

```sql
CREATE VIEW active AS SELECT * FROM users WHERE status = 'active' WITH CHECK OPTION;
-- INSERT/UPDATE that sets status <> 'active' → ERROR 1369: CHECK OPTION failed
```

For views layered on views, `LOCAL` checks only this view's predicate; `CASCADED` (the default) also checks every underlying view's predicate.

## `ALTER VIEW` and `DROP VIEW`

```sql
ALTER VIEW view_name [(column_list)] AS select_statement …;   -- same options as CREATE, minus OR REPLACE / IF NOT EXISTS
DROP VIEW [IF EXISTS] view_name [, view_name] … [RESTRICT | CASCADE];
```

- `ALTER VIEW` is equivalent to `CREATE OR REPLACE VIEW` on an existing view.
- In `DROP VIEW`, `RESTRICT` and `CASCADE` are **parsed but ignored** (accepted only for syntax compatibility).
- `DROP VIEW IF EXISTS` downgrades a missing view to a note; in a multi-view drop, existing views are still dropped.

## See Also

- **`mariadb-select`** — the view body is a `SELECT`; the clauses that make a view non-updatable or non-mergeable are all `SELECT` features
- **`mariadb-create-table`** — shares the `CREATE OR REPLACE` / `IF NOT EXISTS` atomic-DDL model and the same mutual-exclusion rule
- **`mariadb-query-optimization`** (topical) — when the `MERGE` vs `TEMPTABLE` choice matters for performance
- **`mariadb-system-versioned-tables`** (topical) — a view adds no row history; put `WITH SYSTEM VERSIONING` on the base table and query it with `FOR SYSTEM_TIME`
- Canonical references on `mariadb.com/docs` (consult only for edge cases not covered here):
  - <https://mariadb.com/docs/server/server-usage/views/create-view>
  - <https://mariadb.com/docs/server/server-usage/views/view-algorithms>
  - <https://mariadb.com/docs/server/server-usage/views/inserting-and-updating-with-views>
  - <https://mariadb.com/docs/server/server-usage/views/alter-view>
  - <https://mariadb.com/docs/server/server-usage/views/drop-view>

<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>