Skip to main content
Previous sectionNext section

DROP TABLE

Deletes a table and (optionally) its data.

Synopsis

DROP TABLE table 
     [RESTRICT | CASCADE] [%DELDATA | %NODELDATA]

Arguments

Argument Description
table The name of the table to be deleted. The table name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name. Schema search path values are not used.
RESTRICT
CASCADE
Optional — RESTRICT only allows a table with no dependent views or integrity constraints to be deleted. CASCADE allow a table with dependent views or integrity constraints to be deleted; any referencing views or integrity constraints will also be deleted as part of the table deletion. The CASCADE keyword option is not supported for foreign key constraints.
%DELDATA
%NODELDATA
Optional — These keywords specify whether to delete data associated with a table when deleting the table. The default is to delete table data.

Description

The DROP TABLE command deletes a table and its corresponding persistent class definition. If the table is the last item in its schema, deleting the table also deletes the schema and its corresponding persistent class package.

By default, DROP TABLE deletes both the table definition and the table’s data (if any exists). The %NODELDATA keyword allows you to specify deletion of the table definition but not the table’s data.

DROP TABLE deletes all indices and triggers associated with the table.

In order to delete a table, the following conditions must be met:

  • The table must exist in the current namespace. Attempting to delete a non-existent table generates an SQLCODE -30 error.

  • The table definition must be modifiable. If the class that projects the table is defined without DdlAllowed, attempting to delete the table generates an SQLCODE -300 error.

  • The table must not be locked by another concurrent process. If the table is locked, DROP TABLE waits indefinitely for the lock to be released. If lock contention is a possibility, it is important that you LOCK the table IN EXCLUSIVE MODE before issuing a DROP TABLE.

  • You must have the necessary privileges to delete the table. Attempting to delete a table without the necessary privileges generates an SQLCODE -99 error.

You can use the $SYSTEM.SQL.DropTable() method to delete a table in the current namespace. You specify the SQL table name. Unlike DROP TABLE, this method can delete a table that was defined without [DdlAllowed]. The second argument specifies whether the table data should also be deleted; by default, data is not deleted.

  DO $SYSTEM.SQL.DropTable("Sample.MyTable",1,.SQLCODE,.%msg)
  IF SQLCODE '= 0 {WRITE "SQLCODE ",SQLCODE," error: ",%msg}
Copy code to clipboard

You can use the $SYSTEM.OBJ.Delete() method to delete one or more tables in the current namespace. You must specify the persistent class name that projects the table (not the SQL table name). You can specify multiple class names using wildcards. The second argument specifies whether the table data should also be deleted; by default, data is not deleted.

Privileges

The DROP TABLE command is a privileged operation. The user must have %DROP_TABLE administrative privilege to execute DROP TABLE. Failing to do so results in an SQLCODE –99 error with the %msg User does not have %DROP_TABLE privileges. You can use the GRANT command to assign %DROP_TABLE privileges, if you hold appropriate granting privileges.

It is not necessary for the user to have DELETE object privilege for the specified table, even when the DROP TABLE operation deletes both the table and the table data.

In embedded SQL, you can use the $SYSTEM.Security.Login() method to log in as a user with appropriate privileges:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )
Copy code to clipboard

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.Security in the InterSystems Class Reference.

DROP TABLE cannot be used on a table created by defining a persistent class, unless the table class definition includes [DdlAllowed]. Otherwise, the operation fails with an SQLCODE -300 error with the %msg DDL not enabled for class 'Schema.tablename'.

Existing Object Privileges

Deleting a table does not delete the object privileges for that table. For example, the privilege granted to a user to insert, update, or delete data on that table. This has the following two consequences:

  • If a table is deleted, and then another table with the same name is created, users and roles will have the same privileges on the new table that they had on the old table.

  • Once a table is deleted, it is not possible to revoke object privileges for that table.

For these reasons, it is generally recommended that you use the REVOKE command to revoke object privileges from a table before deleting the table.

Table Containing Data

By default, DROP TABLE deletes the table definition and deletes the table’s data. This table data delete is an atomic operation; if DROP TABLE encounters data that cannot be deleted (for example, a row with a referential constraint) any data deletion already performed is automatically rolled back, with the result that no table data is deleted.

The deletion of data can be overridden on a per-table basis, or system-wide. When deleting a table, you can specify DROP TABLE with the %NODELDATA option to prevent the automatic deletion of the table’s data. The default system configuration setting is to delete table data. If, however, the system-wide default is set to not delete table data, you can delete data on a per-table basis by specifying DROP TABLE with the %DELDATA option.

You can set the system-wide default for table data deletion as follows:

  • The $SYSTEM.SQL.SetDDLDropTabDelData() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays a Does DDL DROP TABLE delete the table's data? setting.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Does DDL DROP TABLE Delete the Table’s Data.

The default is “Yes” (1). This is the recommended setting for this option. Set this option to “No” (0) if you want DROP TABLE to not delete the table’s data when it deletes the table definition.

You can use the TRUNCATE TABLE command to delete the table’s data without deleting the table definition.

Lock Applied

The DROP TABLE statement acquires an exclusive table-level lock on table. This prevents other processes from modifying the table definition or the table data while table deletion is in process. This table-level lock is sufficient for deleting both the table definition and the table data; DROP TABLE does not acquire a lock on each row of the table data. This lock is automatically released at the end of the DROP TABLE operation.

Foreign Key Constraints

By default, you cannot drop a table if any foreign key constraints are defined on another table that references the table you are attempting to drop. You must drop all referencing foreign key constraints before dropping the table they reference. Failing to delete these foreign key constraints before attempting a DROP TABLE operation results in an SQLCODE -320 error.

This default behavior is consistent with the RESTRICT keyword option. The CASCADE keyword option is not supported for foreign key constraints.

To change this default foreign key constraint behavior, refer to the COMPILEMODE=NOCHECK option of the SET OPTION command.

Associated Queries

Dropping a table automatically purges any related cached queries and purges query information as stored in %SYS.PTools.SQLQuery. Dropping a table automatically purges any SQL runtime statistics (SQL Stats) information for any related query.

Nonexistent Table

To determine if a specified table exists in the current namespace, use the $SYSTEM.SQL.TableExists() method.

If you try to delete a nonexistent table, DROP TABLE issues an SQLCODE -30 error by default. However, this error-reporting behavior can be overridden by setting the system configuration as follows:

  • The $SYSTEM.SQL.SetDDLNo30() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays a Allow DDL DROP of non-existent table or view setting.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Allow DDL DROP of Non-existent Table or View.

The default is “No” (0). This is the recommended setting for this option. Set this option to “Yes” (1) if you want a DROP TABLE for nonexistent table to perform no operation and not issue an error message.

Examples

The following embedded SQL example creates a table named SQLUser.MyEmployees and later deletes it. This example specifies that any data associated with this table not be deleted when the table is deleted:

  &sql(CREATE TABLE SQLUser.MyEmployees (
       NAMELAST     CHAR (30) NOT NULL,
       NAMEFIRST    CHAR (30) NOT NULL,
       STARTDATE    TIMESTAMP,
       SALARY       MONEY))
  WRITE !,"Created a table"
  /*
    &sql(SQL code populating SQLUser.MyEmployees table)
    &sql(SQL code using SQLUser.MyEmployees table)
  */
  NEW SQLCODE,%msg
  &sql(DROP TABLE SQLUser.MyEmployees %NODELDATA)
  IF SQLCODE=0 {WRITE !,"Table deleted"}
  ELSE {WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
Copy code to clipboard

See Also