CREATE VIEW

Purpose

This statement is used to create a view.

Limitations and considerations

• A view is not stored as a table in the database. Instead, it is derived from the SELECT statement specified in the CREATE VIEW statement. Each time the view is used, it is re-derived.

• If the FROM clause references two or more tables or views, the view is called a join view. An updatable join view, also known as a modifiable join view, involves two or more base tables or views and allows DML operations. Updatable views are not restricted by the WITH READ ONLY clause.

• To ensure the updatable nature of a view, a series of specific conditions must be met. One universally applicable rule is that when performing INSERT, UPDATE, or DELETE operations on a join view, the operations can only modify a single base table.

Privilege requirements

To execute the CREATE VIEW statement, the current user must have the CREATE VIEW privilege. For more information about OceanBase Database privileges, see Privilege classification in MySQL mode.

Syntax

CREATE [OR REPLACE] VIEW view_name [(column_name_list)]
    AS select_stmt
    [view_check_option];

column_name_list:
    column_name [, column_name ...]

view_check_option:
    WITH [LOCAL | CASCADED] CHECK OPTION

Parameters

view_check_option

WITH CHECK OPTION is a syntax used to create a view, ensuring that inserted or updated data meets the view's definition conditions. The WITH CHECK OPTION clause can also be used with the LOCAL or CASCADED keywords to further specify the scope of data checks. The difference between these two keywords lies in the scope of data checks:

• LOCAL: Checks only the current view. Whether to check the referenced views depends on their definitions.
• CASCADED: The default value. Checks the current view and recursively checks all referenced views.

Starting from V4.2.5 BP7, the WITH CHECK OPTION clause is supported when the view filter condition (where_clause) includes subqueries.

Examples

Create the test tables test_tbl1 and test_tbl2.

obclient> CREATE TABLE test_tbl1 (col1 INT, col2 VARCHAR(50), col3 INT);

obclient> CREATE TABLE test_tbl2 (col1 INT, col2 VARCHAR(50));

• Create a view v1_t1 by selecting the col1 and col2 columns from table test_tbl1.

  obclient> CREATE OR REPLACE VIEW v1_t1
      AS SELECT col1, col2
         FROM test_tbl1;
  

• Create a view v2_t1 using the WITH LOCAL CHECK OPTION clause.

  obclient> CREATE OR REPLACE VIEW v2_t1
      AS SELECT *
         FROM test_tbl1
         WHERE col1 > 0 WITH LOCAL CHECK OPTION;
  

• Create a view v3_t1 using the WITH CASCADED CHECK OPTION clause.

  obclient> CREATE OR REPLACE VIEW v3_t1
      AS SELECT *
         FROM test_tbl1
         WHERE col1 > 1 WITH CASCADED CHECK OPTION;
  

• Create a view v4_t1 using the WITH CHECK OPTION clause.

  obclient> CREATE OR REPLACE VIEW v4_t1
      AS SELECT *
         FROM test_tbl1
         WHERE col1 > 10 WITH CHECK OPTION;
  

• Specify the WITH CHECK OPTION clause after the filter condition subquery.

  obclient> CREATE OR REPLACE VIEW v5_t1_t2
      AS SELECT *
      FROM test_tbl1 t1
      WHERE EXISTS (SELECT 1 FROM test_tbl2 t2 WHERE t2.col1 = t1.col1) WITH CHECK OPTION;
  

References

Manage views