Open topic with navigation
DROP VIEW — remove a view.
DROP VIEW [IF EXISTS] [CASCADE | RESTRICT] [
view_nameDROP VIEW [
view_name[IF EXISTS] [CASCADE | RESTRICT]
DROP VIEW drops an existing view from a database. To execute this command, you must be the owner of the view.
You can change the definition of a view by dropping and recreating it. You cannot drop a view that is referenced in another database object, such as another view, without using the
CASCADE option. This will drop the view and all other views that reference the view being dropped.
RESTRICT can occur before or after
CREATE VIEW for information about how to create views.
Caution: If DDL statements are to be executed concurrently with DML statements, ensure that a
LOCK TABLE command is successfully executed prior to executing the DDL statement.
Optional. The name of the schema that owns the view to be dropped. If schema is not provided, the view must be owned by the current schema.
Name (optionally schema-qualified) of an existing view.
Automatically drops the view and all other views that reference the view being dropped. Currently, this does not automatically drop user defined functions or stored procedures that reference the view being dropped.
Refuse to drop the view if there are any database objects that reference the view being dropped. This is the default.
Note: Currently, a view will be dropped using the
RESTRICT option despite being referenced in a stored procedure. It is recommended that stored procedures be checked for the existence of a particular view prior to dropping the view by running a query against the system table that maintains stored procedures:
SELECT procedurename, schema, creator FROM system.procedures WHERE LOWER(proceduretext) LIKE '%;
viewname is the name of the view in lowercase.
If the view does not exist and you specify
IF EXISTS, NuoDB does not generate an error. Otherwise, if the view does not exist, an error is generated.
USE TEST DROP TABLE IF EXISTS table1; DROP VIEW IF EXISTS view1; CREATE TABLE table1 (column1 INTEGER); CREATE VIEW view1 AS SELECT column1 FROM table1; DROP VIEW view1; DROP VIEW nosuchview;can't find view "NOSUCHVIEW"
DROP VIEW nosuchview IF EXISTS;
USE TEST DROP TABLE IF EXISTS table1; DROP VIEW IF EXISTS view1; DROP VIEW IF EXISTS view2; CREATE TABLE table1 (column1 INTEGER); CREATE VIEW view1 AS SELECT column1 FROM table1; CREATE VIEW view2 AS SELECT column1 FROM view1; DROP VIEW view1;can't drop view TEST.VIEW1 -- referenced in view for TEST.VIEW2
DROP VIEW view1 CASCADE; DROP VIEW view2;can't find view "VIEW2"