Alter View
Usage
SQL
ALTER [PRIVATE] VIEW <schema_name>.<viewname>[column_name column_type, ..., PRIMARY KEY(column_name), FOREIGN KEY(column_name) REFERENCES another view(column name) ] AS queryExpression;;Syntax Rules
The
ALTERquery expression may be prefixed with a cache hint for materialized view definitions. The hint will take effect the next time the materialized view table is loaded;PRIMARY KEY, which uniquely identifies each record in a table, must contain a unique value and cannot be null;FOREIGN KEY, which refers to the primary key of another view;The number of columns in
queryExpressionmust not be changed.
Please note that PRIMARY KEY and FOREIGN KEY are not enforced for views. For example, if a primary key column contains duplicate values, the Server will not throw any errors.
The PRIVATE keyword is reserved for internal use and should not be specified for user-defined views.
Example
SQL
CREATE VIEW views.creditcard AS SELECT * FROM adventureworks.creditcard;
ALTER VIEW views.creditcard AS SELECT * from (SELECT * FROM adventureworks.creditcard) a;
ALTER VIEW views.creditcard(a integer, b integer, PRIMARY KEY(a)) AS SELECT * FROM adventureworks.creditcard;
ALTER VIEW views.userOrder(userId INTEGER, userName INTEGER, orderId INTEGER, FOREIGN KEY(orderId) REFERENCES views.orders(orderId)) AS
SELECT u.userId, u.userName, o.orderId
FROM views.users u
JOIN views.orders o ON u.userId = o.userId;;