Skip to main content
SereneDBSereneDB
897Contact Us

DROP

The DROP statement removes a catalog entry added previously with the CREATE command.

Examples​

Delete the table with the name tbl:

SQL
DROP TABLE tbl;

Drop the view with the name view1; do not throw an error if the view does not exist:

SQL
DROP VIEW IF EXISTS view1;

Drop function fn:

SQL
DROP FUNCTION fn;

Drop index idx:

SQL
DROP INDEX idx;

Drop schema sch:

SQL
DROP SCHEMA sch;

Drop sequence seq:

SQL
DROP SEQUENCE seq;

Drop macro mcr:

SQL
DROP MACRO mcr;

Drop macro table mt:

SQL
DROP MACRO TABLE mt;

Drop type typ:

SQL
DROP TYPE typ;

Drop text search dictionary dict:

SQL
DROP TEXT SEARCH DICTIONARY dict;

Syntax​

Syntax

Dependencies of Dropped Objects​

SereneDB performs limited dependency tracking for some object types. By default or if the RESTRICT clause is provided, the entry will not be dropped if there are any other objects that depend on it. If the CASCADE clause is provided then all the objects that are dependent on the object will be dropped as well.

SQL
CREATE SCHEMA myschema;
CREATE TABLE myschema.t1 (i INTEGER);
DROP SCHEMA myschema;
error (?i)cannot drop entry "myschema" because there are entries that depend on it

The CASCADE modifier drops both myschema and myschema.t1:

SQL
CREATE SCHEMA myschema;
CREATE TABLE myschema.t1 (i INTEGER);
DROP SCHEMA myschema CASCADE;

The following dependencies are tracked and thus will raise an error if the user tries to drop the depending object without the CASCADE modifier.

Depending object typeDependent object type
SCHEMAFUNCTION
SCHEMAMACRO TABLE
SCHEMAMACRO
SCHEMASCHEMA
SCHEMASEQUENCE
SCHEMATABLE
SCHEMATYPE
SCHEMAVIEW
TABLEVIEW
TEXT SEARCH DICTIONARYINDEX

Dependencies on Views​

Views that reference a table are tracked as dependents of that table. If a view references a table and the table is dropped with RESTRICT (the default), then the drop is rejected and the view stays valid:

SQL
CREATE TABLE tbl (i INTEGER);
CREATE VIEW view1 AS    SELECT i FROM tbl;
DROP TABLE tbl RESTRICT;
SELECT * FROM view1;
db error: ERROR: Cannot drop entry "tbl" because there are entries that depend on it.
view "view1" depends on table "tbl".
Use DROP...CASCADE to drop all dependents.

i

Dependencies across Databases​

A view or macro may read objects of another database, and a table may take a column default (nextval) or a column type from one. Those references are tracked the same way: dropping the referenced object with RESTRICT is rejected, and so is DROP DATABASE while an object of another database depends on one of its objects. CASCADE also drops a dependent view or macro in the other database; a dependent table stays, and a column default that used a dropped sequence is removed:

SQL
CREATE DATABASE sales;
CREATE TABLE sales.public.orders (id INTEGER, amount INTEGER);
CREATE VIEW sales_report AS    SELECT sum(amount) AS total FROM sales.public.orders;
DROP TABLE sales.public.orders;
DROP DATABASE sales;
DROP TABLE sales.public.orders CASCADE;
DROP DATABASE sales;
db error: ERROR: Cannot drop entry "orders" because there are entries that depend on it.
view "sales_report" depends on table "orders".
Use DROP...CASCADE to drop all dependents.

db error: ERROR: Cannot drop entry "sales" because there are entries that depend on it.
view "sales_report" depends on database "sales".
Use DROP...CASCADE to drop all dependents.

DROP DATABASE ... CASCADE drops the dependents in other databases together with the database:

SQL
CREATE DATABASE sales;
CREATE TABLE sales.public.orders (id INTEGER, amount INTEGER);
CREATE VIEW sales_report AS    SELECT sum(amount) AS total FROM sales.public.orders;
DROP DATABASE sales CASCADE;
SELECT count(*) AS reports FROM information_schema.views WHERE table_name = 'sales_report';
 reports
---------
       0

The same holds between concurrent transactions: when one transaction drops an object and another adds a reference to it from another database, whichever commits second fails at COMMIT and changes nothing.

Limitations on Reclaiming Disk Space​

Running DROP TABLE should free the memory used by the table, but not always disk space. Even if disk space does not decrease, the free blocks will be marked as free. For example, if we have a 2 GB file and we drop a 1 GB table, the file might still be 2 GB, but it should have 1 GB of free blocks in it. To check this, use the following PRAGMA and check the number of free_blocks in the output:

SQL
PRAGMA database_size;

To reclaim space after dropping a table, use the CHECKPOINT statement or compact the database by creating a fresh copy with the COPY FROM DATABASE statement.

Edit this page ↗