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.
-
The table must either have no associated views or DROP TABLE must specify the CASCADE keyword. Attempting
to delete a table with associated views without CASCADE generates
an SQLCODE -321 error.
-
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 delete a table even if
the corresponding class is defined as a deployed class.
-
You cannot delete a table if the persistent class
that projects the table has derived classes (subclasses). Attempting
to delete a superclass that would leave a subclass orphaned generates
an SQLCODE -300 error with a message: Class 'MySuperClass'
has derived classes and therefore cannot be dropped via DDL.
You can use the $SYSTEM.SQL.Schema.DropTable()Opens in a new tab 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.Schema.DropTable("Sample.MyTable",1,.SQLCODE,.%msg)
IF SQLCODE '= 0 {WRITE "SQLCODE ",SQLCODE," error: ",%msg}
You can use the $SYSTEM.OBJ.Delete()Opens in a new tab 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()Opens in a new tab method to log in as a user with
appropriate privileges:
DO $SYSTEM.Security.Login("_SYSTEM","SYS")
&sql( )
You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.SecurityOpens in a new tab 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.
You can set the system-wide default for table data deletion
using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method DDLDropTabDelData option. To determine the current setting,
call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays
the Does DDL DROP TABLE delete the table's data? setting.
The default is 1 (“Yes”). This is the recommended
setting for this option. Set this option to 0 (“No”)
if you want DROP TABLE to not delete the table’s
data when it deletes the table definition.
The deletion of data can be overridden on a per-table basis.
When deleting a table, you can specify DROP TABLE with the %NODELDATA option to prevent the automatic deletion of
the table’s data. If 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.
In most circumstances DROP TABLE automatically
deletes the table’s data using a highly efficient kill extent
operation. The following circumstances prevent the use of kill extent:
the table has foreign keys that reference it; the class projecting
the table is a subclass of a persistent class; the class does not
use default storage; there is a ForEach = "row/object" trigger; there
is a stream field that references a non-default stream field global location. If any
of these apply, DROP TABLE deletes the table’s
data using a less-efficient delete record operation.
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.
Nonexistent Table
To determine if a specified table exists in the current namespace,
use the $SYSTEM.SQL.Schema.TableExists()Opens in a new tab method.
By default, if you try to delete a nonexistent table, DROP TABLE issues an SQLCODE -30 error. This is the recommended
setting. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL DROP of non-existent table or view setting. The
default is 0 (“No”). If this option is set to 1 (“Yes”), DROP TABLE for a nonexistent table
performs no operation and does not issue an error message.
From the Management Portal, System Administration, Configuration, SQL and Object Settings, SQL you can set this option (and other similar create, alter, and drop
options) system-wide by selecting the Ignore redundant DDL
statements check box.
