Table of Contents
CREATE VIEW statement is used to create a virtual table in the database.
view_definition : CREATE [OR REPLACE] VIEW view_name [ (column_name_list) ] AS query_expression [ WITH CHECK OPTION ]
This clause forces dropping of an existed table with the specified name and then new empty table will be create as specified. So effectively this is the same as
DROP TABLE T; CREATE TABLE T ....
ATTENTION: 'CREATE OR REPLACE' will destroy all sub-objects of the existed view, such as triggers, constraints. If you need preserve sub-objects, use ALTER VIEW command.
If table do not exists, then nothing happens.
NOTE: 'OR REPLACE' syntax Valentina have take from Oracle/Postgre world.
IF NOT EXISTS
The IF NOT EXISTS option allows you supress the error message in case if such view already exists. This makes it much easier to perform SQL dumps without interruption.
NOTE: 'IF NOT EXISTS' syntax Valentina have take from mySQL world.
Specifies the name of new view. A view belongs to a database. Tables and Views share the same namespace within a database, so a database cannot contain a table and a view that have the same name.
Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement.
Columns retrieved by the SELECT statement can be simple references to table columns. They can also be expressions that use functions, constant values, operators, and so forth.
Specifies a SELECT statement that provides the definition of the view.
WITH CHECK OPTION
WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.
- Valentina 3.0 have read-only views.
CREATE VIEW wMale AS SELECT * FROM tblPerson WHERE fldGender = 'M';