Skip to main content
Version: V1.1.0

CREATE VIEW

Description​

This statement is used to create a view.

Limitations and Considerations​

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

  • If two or more tables or views are referenced in the FROM clause, 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 limited by the WITH READ ONLY clause.

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

Permissions​

To execute the CREATE VIEW statement, the current user must have the CREATE VIEW permission. For more information about seekdb permissions, see seekdb Permission Classification.

Syntax​

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

column_name_list:
    column_name [, column_name ...]

check_option:
    WITH CHECK OPTION
    | WITH CASCADED CHECK OPTION
    | WITH LOCAL CHECK OPTION

Parameter Description​

ParameterDescription
OR REPLACEOptional. Specifies that if a view with the specified name already exists, it will be replaced with the new definition.
view_nameSpecifies the name of the view.
column_name_listOptional. Specifies the list of column names for the view. If not specified, the column names retrieved by the SELECT statement will be used as the view's column names. The columns retrieved by the SELECT statement can be simple references to table columns or expressions involving functions, constants, operators, etc. View column names have the following restrictions:
  • Column names must be unique and cannot be repeated.
  • The number of names in column_name_list must equal the number of columns retrieved by the SELECT statement.
select_stmtThe query (SELECT) statement used to define the view. It specifies the view's definition, allowing selection from base tables or other views. For specific structures and options of the query statement, see SELECT Statement.
check_optionOptional. Specifies the check scope for the view, used to restrict insert and update operations on the view. For more details on the check scope, see check_option below.

check_option​

WITH CHECK OPTION: A syntax for creating a view that ensures the data inserted or updated meets the view's definition conditions. In MySQL mode, the WITH CHECK OPTION clause can also be used with the LOCAL/CASCADED keywords to further specify the scope of data checks.

  • WITH CASCADED CHECK OPTION: Checks the current view and recursively checks all referenced views.

  • WITH LOCAL CHECK OPTION: Checks the current view. Whether to check the referenced views depends on their definitions.

Examples​

  1. Create a table named test_tbl1.

    CREATE TABLE test_tbl1 (col1 INT, col2 INT);

  2. Create a view named v1 based on the test_tbl1 table.

    CREATE VIEW v1
        AS SELECT * FROM test_tbl1 WHERE col2 < 2
        WITH CHECK OPTION;

  3. Create a view named v2 based on the v1 view using the LOCAL CHECK OPTION syntax.

    CREATE VIEW v2
        AS SELECT * FROM v1 WHERE col2 > 0
        WITH LOCAL CHECK OPTION;

  4. Create a view named v3 based on the v1 view using the CASCADED CHECK OPTION syntax.

    CREATE VIEW v3
        AS SELECT * FROM v1 WHERE col2 > 0
        WITH CASCADED CHECK OPTION;

References​

Contents

Edit this page