SQL Statements
DROP CAST
Use the DROP CAST statement to remove a previously defined cast from the database.
Syntax
Usage
You must be the owner of the cast or have the DBA privilege to use the DROP CAST statement.
What Happens When You Drop a Cast
When you drop a cast, the cast definition is removed from the database. Once you drop a cast, it cannot be invoked either explicitly or implicitly. Dropping a cast has no effect on the function associated with the cast. Use the DROP FUNCTION statement to remove a function from the database.
A cast that is defined on a particular data type can also be used on any distinct types created from that type. When you drop the cast, you can no longer invoke it for the distinct types. Dropping a cast that is defined for a distinct type has no effect on casts for its source type.
When you create a distinct type, the database server automatically defines an explicit cast from the distinct type to its source type and another explicit cast from the source type to the distinct type. When you drop the distinct type, the database server automatically drops these two casts.
References
See the CREATE CAST statement in this manual for information about creating a cast.
See the DROP FUNCTION statement in this manual for information about how to remove a function that is used to implement a cast.
See the Data Types segment in this manual and Chapter 3, "Environment Variables" in the Informix Guide to SQL: Syntax for information about data types.
DROP DATABASE
Use the DROP DATABASE statement to delete an entire database, including all system catalog tables, indexes, and data.
Syntax
Usage
You must have the DBA privilege or be user informix to run the DROP DATABASE statement successfully. Otherwise, the database server issues an error message and does not drop the database.
You cannot drop the current database or a database that is being used by another user. All the database users must first execute the CLOSE DATABASE statement.
The DROP DATABASE statement cannot appear in a multistatement PREPARE statement.
The following statement drops the stores7 database:
When you drop a database with transactions, the transaction-log file that is associated with the database is removed.
The DROP DATABASE statement does not remove the database directory if it includes any files other than those created for database tables and their indexes.
You can specify the full pathname of the database in quotes, as the following example shows:
You cannot use a ROLLBACK WORK statement to undo a DROP DATABASE statement. If you roll back a transaction that contains a DROP DATABASE statement, the database is not re-created, and you do not receive an error
message.
Use this statement with caution. DB-Access does not prompt you to verify that you want to delete the entire database. 
You can use a simple database name in a program or host variable, or you can use the full database server and database name. See "Database Name" for more information.
References
See the CREATE DATABASE and CLOSE DATABASE statements in this manual.
DROP FUNCTION
Use the DROP FUNCTION statement to remove an external function or anSPL function from the database.
Syntax
Usage
A function is a user-defined routine that returns one or more values. In INFORMIX-Universal Server, you can write functions in Stored Procedure Language (SPL) or in an external language, such as C.
Because you can overload routines in INFORMIX-Universal Server, you can define more than one function with the same name but with different parameter lists. Therefore, a function name alone might not identify a function. In that case, you must specify one of the following in the DROP FUNCTION statement:
The keyword FUNCTION, the function name, and the number, type, and order of parameters (as they appear from left to right in the DROP FUNCTION statement) make up the function signature. The function signature unambiguously identifies the function. For a given function, at least one item in the signature must be unique among all the functions stored in a name space or database.
Dropping a function removes the text and executable versions of the function.
You cannot use DROP FUNCTION to drop any type of procedure.
You can also use DROP ROUTINE to drop a function. For more information on DROP ROUTINE, see page 1-368.
Function Name
The function name can be the name of any user-defined function stored on the local database server. You can use a fully qualified function name to drop a function stored on a remote server, if either of the following conditions is true:
You cannot drop a remote function if any of its parameters are opaque, distinct, collection, or row types.
The syntax of the function name is described in the Function Name segment on page 1-963.
Specific Name
A specific name uniquely identifies the function within the database. If you use the DROP SPECIFIC FUNCTION statement, you must use the function's specific name as it is defined in the CREATE FUNCTION statement.
With DROP SPECIFIC FUNCTION, you must use the specific name of a function. You cannot use the specific name of a procedure.
The syntax of the specific name is described in the Specific Name segment on page 1-1038.
Required Permissions
You must be the owner of the function or have the DBA privilege to use the DROP FUNCTION statement.
Examples
If you use parameter data types to identify a function, they follow the function name, as in the following example:
If you use the specific name for the function, you must use the keyword SPECIFIC, as in the following example:
SPL Functions
Because you cannot change the text of an SPL function, you must drop it using DROP FUNCTION or DROP ROUTINE and then re-create it using CREATE FUNCTION. Make sure that you have a copy of the SPL function text somewhere outside the database, in case you want to re-create it after it is dropped.
You cannot drop an SPL function within the same SPL function.
References
In this manual, see the CREATE FUNCTION, CREATE FUNCTION FROM, DROP FUNCTION, DROP ROUTINE, and EXECUTE FUNCTION statements.
In the Informix Guide to SQL: Tutorial, see the discussion of user-defined routines in Chapter 14 and the discussion of SPL routines in Chapter 14.
DROP INDEX
Use the DROP INDEX statement to remove a previously defined index from the database.
Syntax
Usage
You must be the owner of the index or have the DBA privilege to use the DROP INDEX statement.
The following example drops the index o_num_ix that joed owns. The stores7 database must be the current database.
You cannot use the DROP INDEX statement on a column or columns to drop a unique constraint that is created with a CREATE TABLE statement; you must use the ALTER TABLE statement to remove indexes that are created as constraints with a CREATE TABLE or ALTER TABLE statement.
The index is not actually dropped if it is shared by constraints. Instead, it is renamed in the sysindexes system catalog table with the following format:
In this example, tabid and constraint_id are from the systables and sysconstraints system catalog tables, respectively. The idxname (index name) column in the sysconstraints table is then updated to reflect this change. For example, the renamed index name might be something like the following (quotes used to show the spaces):
If this index is a unique index with only referential constraints sharing it, the index is downgraded to a duplicate index after it is renamed.
References
See the ALTER TABLE, CREATE INDEX, and CREATE TABLE statements in this manual.
In the INFORMIX-Universal Server Performance Guide, see the discussion of indexes.
DROP OPCLASS
Use the DROP OPCLASS statement to remove an existing operator class from the database.
Syntax
Usage
You must be the owner of the operator class or have DBA privilege to use the DROP OPCLASS statement.
The RESTRICT keyword is required with the DROP OPCLASS statement. RESTRICT causes DROP OPCLASS to fail if the database contains indexes or secondary access methods that use the opclass name operator class. The DROP OPCLASS statement cannot drop these indexes or the access methods.
The following DROP OPCLASS statement drops an operator class called abs_btree_ops:
References
See CREATE OPCLASS in this manual.
For information on how to create or extend an operator class, see the Extending INFORMIX-Universal Server: Data Types manual.
DROP PROCEDURE
Use the DROP PROCEDURE statement to remove an external procedure or an SPL procedure from the database.
Syntax
Usage
A procedure is a user-defined routine that does not return a value. In INFORMIX-Universal Server, you can write procedures in Stored Procedure Language (SPL) or in an external language, such as C.
Because you can overload routines in INFORMIX-Universal Server, you can define more than one procedure with the same name but with different parameter lists. Therefore, a procedure name alone might not identify a procedure. In that case, you must specify one of the following in the DROP PROCEDURE statement:
The keyword PROCEDURE, the procedure name, and the number, type, and order of parameters (as they appear from left to right in the DROP PROCEDURE statement) make up the signature for the procedure. The procedure signature unambiguously identifies the procedure. For a given procedure, at least one item in the signature must be unique among all the procedures stored in a name space or database.
Dropping a procedure removes the text and executable versions of the procedure.
You can also use DROP ROUTINE to drop a procedure. For more information on DROP ROUTINE, see page 1-368.
Procedure Name
The procedure name can be the name of any user-defined procedure stored on the local database server. You can use a fully qualified procedure name to drop a procedure stored on a remote server, if either of the following conditions is true:
You cannot drop a remote procedure if any of its parameters are opaque, distinct, collection, or row types.
The syntax of a procedure name, including a fully qualified procedure name, is described in the Procedure Name segment on page 1-1008.
Specific Name
A specific name uniquely identifies the procedure within the database. If you use the DROP SPECIFIC PROCEDURE statement, you must use the specific name for the procedure as it is defined in the CREATE PROCEDURE statement.
When you use DROP SPECIFIC PROCEDURE with SPL routines, you can use the name of an SPL procedure or SPL function. This feature provides backward compatibility with earlier Informix products and is described in "SPL: SPL Backward Compatibility Option".
When you use DROP SPECIFIC PROCEDURE with external routines, you must use the specific name of a procedure. You cannot use the specific name of a function.
The syntax of the specific name is described in the Specific Name segment on page 1-1038.
Required Permissions
You must be the owner of the procedure or have the DBA privilege to use the DROP PROCEDURE statement.
Examples
If you use parameter data types to identify a procedure, they follow the procedure name, as in the following example:
If you use the specific name for the procedure, you must use the keyword SPECIFIC, as in the following example:
SPL Procedures
Because you cannot change the text of an SPL function, you must drop it using DROP PROCEDURE or DROP ROUTINE and then recreate it using CREATE PROCEDURE. If you want to recreate it after it is dropped, make sure that you have a copy of the SPL procedure text somewhere outside the database.
You cannot drop an SPL procedure within the same SPL procedure.
SPL Backward Compatibility Option
For backward compatibility with earlier Informix products, you can use DROP PROCEDURE to drop an SPL function (that is, an SPL routine that returns a value). However, Informix recommends that you use DROP PROCEDURE only with procedures. You can also use DROP FUNCTION or DROP ROUTINE to drop an SPL function.
References
In this manual, see the CREATE PROCEDURE, CREATE PROCEDURE FROM, DROP PROCEDURE, DROP ROUTINE, and EXECUTE PROCEDURE statements.
In the Informix Guide to SQL: Tutorial, see the discussion of user-defined routines in Chapter 14 and the discussion of SPL routines in Chapter 14.
DROP ROLE
Use the DROP ROLE statement to remove a previously created role from the database.
Syntax
Usage
The DROP ROLE statement is used to remove an existing role. Either the DBA or a user to whom the role was granted with the WITH GRANT OPTION can issue the DROP ROLE statement.
After a role is dropped, the privileges associated with that role, such as table-level privileges or fragment-level privileges, are dropped, and a user cannot grant or enable a role. If a user is using the privileges of a role when the role is dropped, the user automatically loses those privileges.
A role exists until either the DBA or a user to whom the role was granted with the WITH GRANT OPTION uses the DROP ROLE statement to drop the role.
The following statement drops the role engineer:
References
See the CREATE ROLE, GRANT, REVOKE, and SET ROLE statements in this manual.
DROP ROUTINE
Use the DROP ROUTINE statement to remove any type of user-defined routine from the database.
Syntax
Usage
You can use DROP ROUTINE with any type of routine-an external function, an external procedure, an SPL function, or an SPL procedure. The DROP ROUTINE statement is useful when you do not know whether a routine is a function or a procedure.
Because you can overload routines in INFORMIX-Universal Server, you can define more than one routine with the same name but with different parameter lists. Therefore, a routine name alone might not uniquely identify a routine. In that case, you must specify one of the following in the DROP ROUTINE statement:
The keyword PROCEDURE or FUNCTION, the routine name, and the number, type, and order of parameters (as they appear from left to right in the DROP ROUTINE statement) make up the routine signature. The routine signature unambiguously identifies the routine. For a given routine, at least one item in the signature must be unique among all the routines stored in a name space or database.
Dropping a routine removes the text and executable versions of the routine.
You can also use DROP FUNCTION to drop a function and DROP PROCEDURE to drop a procedure. The DROP FUNCTION statement is described on page 1-354, and the DROP PROCEDURE statement is described on page 1-362.
Procedure Name or Function Name
A procedure name identifies a routine registered with the CREATE PROCEDURE statement and a function name identifies a function registered with the CREATE FUNCTION statement. Without a database qualifier, the routine must reside on the local database server.
You can use a fully qualified procedure name to drop a routine stored on a remote server, if either of the following conditions is true:
You cannot drop a remote procedure if any of its parameters are opaque, distinct, collection, or row types.
For the syntax of a fully qualified name, see "Procedure Name" or "Function Name".
Specific Name
A specific name uniquely identifies a routine within the database. If you use the DROP SPECIFIC ROUTINE statement, you must use the identifier assigned with the SPECIFIC clause of the CREATE PROCEDURE or CREATE FUNCTION statement.
The syntax of Specific Name is described in the Specific Name segment on page 1-1038.
Required Permissions
You must be the owner of the routine or have the DBA privilege to use the DROP ROUTINE statement.
Examples
If you use parameter data types to identify a routine, they follow the routine name, as in the following example:
If you use the specific name for the routine, you must use the keyword SPECIFIC, as in the following example:
SPL Routines
Because you cannot change the text of an SPL routine, you must drop it with DROP PROCEDURE, DROP FUNCTION, or DROP ROUTINE and then re-create it with CREATE PROCEDURE or CREATE FUNCTION. If you want to recreate it after it is dropped, make sure that you have a copy of the SPL routine text somewhere outside the database.
You cannot drop an SPL routine from within the same SPL routine.
References
In this manual, see the CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION, DROP PROCEDURE, EXECUTE FUNCTION, and EXECUTE PROCEDURE statements.
In the Informix Guide to SQL: Tutorial, see the discussion of user-defined routines in Chapter 14 and the discussion of SPL routines in Chapter 14.
DROP ROW TYPE
Use the DROP ROW TYPE statement to remove an existing named row type from the database.
Syntax
Usage
You must be the owner of the row type or have the DBA privilege to use the DROP ROW TYPE statement.
You cannot drop a named row type if the row type name is in use. You cannot drop a named row type when any of the following conditions are true:
To drop a named row type column from a table, use ALTER TABLE.
The DROP ROW TYPE statement does not drop unnamed row types.
The Restrict Keyword
The RESTRICT keyword is required with the DROP ROW TYPE statement. RESTRICT causes DROP ROW TYPE to fail if dependencies on that named row type exist.
The DROP ROW TYPE statement fails and returns an error message if:
Check the systables and syscolumns system catalog tables to find out whether any tables or types use the named row type.
Look in the sysinherits system catalog table to see which types have child types.
Example
The following statement drops the row type named employee_t:
References
See the CREATE ROW TYPE statement in this manual to learn how to create row types.
See the Informix Guide to SQL: Syntax for a description of the system catalog tables.
See Chapter 10 of the Informix Guide to SQL: Tutorial for a discussion of named row types.
DROP SYNONYM
Use the DROP SYNONYM statement to remove a previously defined synonym from the database.
Syntax
Usage
You must be the owner of the synonym or have the DBA privilege to use the DROP SYNONYM statement.
The following statement drops the synonym nj_cust, which cathyg owns:
If a table is dropped, any synonyms that are in the same database as the table and that refer to the table are also dropped.
If a synonym refers to an external table, and the table is dropped, the synonym remains in place until you explicitly drop it using DROP SYNONYM. You can create another table or synonym in place of the dropped table and give the new object the name of the dropped table. The old synonym then refers to the new object. See the CREATE SYNONYM statement for a complete discussion of synonym chaining.
References
See the CREATE SYNONYM statement in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of synonyms in Chapter 11.
DROP TABLE
Use the DROP TABLE statement to remove a previously defined table, along with its associated indexes and data from the database.
Syntax
Usage
You must be the owner of the table or have the DBA privilege to use the DROP TABLE statement.
If you issue a DROP TABLE statement, you are not prompted to verify that you want to delete an entire table.
Effects of DROP TABLE Statement
Use the DROP TABLE statement with caution. When you remove a table, you also delete the data stored in it, the indexes or constraints on the columns (including all the referential constraints placed on its columns), any local synonyms assigned to it, any triggers created for it, and any authorizations you have granted on the table. You also drop all views based on the table and any violations and diagnostics tables associated with the table. You do not remove any synonyms for the table that have been created in an external database.
Specifying CASCADE Mode
The CASCADE mode means that a DROP TABLE statement removes the table and all related database objects, including referential constraints built on the table, views defined on the table, and any violations and diagnostics tables associated with the table. If the table is the supertable in an inheritance hierarchy, CASCADE drops all of the subtables as well as the supertable.
The CASCADE mode is the default mode of the DROP TABLE statement. You can also specify this mode explicitly with the CASCADE keyword.
Specifying RESTRICT Mode
With the RESTRICT keyword, you can control the success or failure of the drop operation for supertables, for tables that have referential constraints and views defined on the table, and for tables that have violations and diagnostics tables associated with the table. Using the RESTRICT option causes the drop operation to fail and an error message to be returned if any of the following conditions are true:
Dropping a Table with Rows That Contain Opaque Data Types
Some opaque data types require special processing when they are deleted. For example, if an opaque data type contains spatial or multi-representational data, it might provide a choice of how to store the data: inside the internal structure or, for very large objects, in a smart large object.
The database server removes opaque types by calling a user-defined support function called destroy(). When you execute the DROP TABLE statement on a table whose rows contain an opaque type, the database server automatically invokes the destroy() function for the type. The destroy() function can perform certain operations on columns of the opaque data type before the table is dropped. For more information about the destroy() support function, see the Extending INFORMIX-Universal Server: Data Types manual.
Tables That Cannot Be Dropped
You cannot drop the following types of tables:
Examples of Dropping a Table
The following example deletes two tables. Both tables are within the current database and are owned by the current user. Neither table has a violations or diagnostics table associated with it. Neither table has a referential constraint or view defined on it.
References
See the CREATE TABLE and DROP DATABASE statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussions of data integrity and creating a table in Chapter 4 and Chapter 9, respectively.
DROP TRIGGER
Use the DROP TRIGGER statement to remove a previously defined trigger definition from the database.
Syntax
Usage
You must be the owner of the trigger or have the DBA privilege to use the DROP TRIGGER statement.
Dropping a trigger removes the text of the trigger definition and the executable trigger from the database.
The following statement drops the items_pct trigger:
You cannot drop a trigger inside a stored procedure if the procedure is called within a data manipulation statement. For example, in the following INSERT statement, a DROP TRIGGER statement is illegal inside the stored procedure proc1:
References
See the CREATE PROCEDURE statement in this manual for more information about a stored procedure that is called within a data manipulation statement.
For more information about triggers, see the CREATE TRIGGER statement in this manual.
DROP TYPE
Use the DROP TYPE statement to remove an existing distinct or opaque data type from the database.
Syntax
Usage
To drop a distinct or opaque type with the DROP TYPE statement, you must be the owner of the data type or have the DBA privilege.
When use the DROP TYPE statement, you remove the type definition from the database (in the sysxtdtypes system catalog table). In general, this statement does not remove any definitions for casts or support functions associated with that data type.
You cannot drop a distinct or opaque type if the database contains any casts, columns, or functions whose definitions reference the type.
The following statement drops the new_type type:
References
See the CREATE DISTINCT TYPE and CREATE OPAQUE TYPE statements in this manual for information. See the CREATE ROW TYPE and DROP ROW TYPE statements in this manual for information about how to define and remove row types from the database. See the CREATE TABLE statement in this manual for more information about creating tables that reference a data type.
See the Data Types segment in this manual for more information about data types.
DROP VIEW
Use the DROP VIEW statement to remove a previously defined view from the database.
Syntax
Usage
You must own the view or have the DBA privilege to use the DROP VIEW statement.
When you drop view name, you also drop all views that have been defined in terms of that view. You can also specify this default condition with the CASCADE keyword.
When you use the RESTRICT keyword in the DROP VIEW statement, the drop operation fails if any existing views are defined on view name, which would be abandoned in the drop operation.
You can query the sysdepend system catalog table to determine which views, if any, depend on another view.
The following statement drops the view that is named cust1:
References
See the CREATE VIEW and DROP TABLE statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of views in Chapter 11.
|
