Replacing existing objects
You can replace an existing object using a CREATE statement rather than always needing to drop the object first.
For many SQL objects, you can optionally replace an existing object when using the CREATE SQL statement. The existing object is effectively dropped before the new object is created. The following SQL statements have that option:
- CREATE ALIAS
- CREATE FUNCTION
- CREATE MASK
- CREATE PERMISSION
- CREATE PROCEDURE
- CREATE SEQUENCE
- CREATE TABLE
- CREATE TRIGGER
- CREATE VARIABLE
- CREATE VIEW
When the replace option is used for any of these statements, the privileges for the existing object are kept. The object definition is replaced by the new definition.
Example: Create or replace sequence
To create a sequence called MYSEQUENCE, or replace a sequence of that name if it exists, use the following SQL statement
CREATE OR REPLACE SEQUENCE MYSEQUENCE AS BIGINT