Drop Table

In this section, you will find information about Oracle's Drop Table statement and their Snowflake equivalent.

Description

A Drop Table statement is used to remove a table. This statement varies a little between Oracle and Snowflake. Please double-check each documentation for more information regarding the differences.

In Oracle, the Drop Table syntax is:

DROP TABLE <table_name> [ CASCADE CONSTRAINTS ] [ PURGE]

In Snowflake, the Drop table syntax is:

DROP TABLE [ IF EXISTS ] <table_name> [ CASCADE | RESTRICT ]

The main difference is that Snowflake does not have an equal for the PURGE clause, as the table will not be permanently removed from the system. Though, the CASCADE CONSTRAINTS and the CASCADE clauses are the same. Both drop the table, even if foreign keys exist that reference this table.

Examples

Now, let's see some code examples, and what it would look like after it has been transformed. Each example uses a different variation of the Drop Table statement.

Example 1:

This example uses the Drop Table statement as simple as possible.

Input Code:

DROP TABLE TEST_TABLE1;

Transformed Code:

DROP TABLE PUBLIC.TEST_TABLE1;

Example 2:

This example uses the Drop Table statement with the PURGE clause. Remember there is no equivalent in Snowflake for the PURGE clause inside a Drop Table statement.

Input Code:

DROP TABLE TEST_TABLE1 PURGE;

Transformed Code:

/*** MSC-WARNING - MSCEWI3084 - PURGE REMOVED FROM DROP TABLE STATEMENT. ***/
DROP TABLE PUBLIC.TEST_TABLE1;

Example 3:

This example uses the Drop Table statement with the CASCADE CONSTRAINTS clause.

Input Code:

DROP TABLE TEST_TABLE1 CASCADE CONSTRAINTS;

Transformed Code:

/*** MSC-WARNING - MSCEWI3085 - CASCADE CONSTRAINTS MODIFIED INSIDE DROP TABLE STATEMENT TO CASCADE. ***/
DROP TABLE PUBLIC.TEST_TABLE1 CASCADE;

In the transformed code, the CONSTRAINTS word is removed from the CASCADE CONSTRAINTS clause.

Example 4:

This example uses the Drop Table statement with the CASCADE CONSTRAINTS and the PURGE clauses.

Input Code:

DROP TABLE TEST_TABLE1 CASCADE CONSTRAINTS PURGE;

Transformed Code:

/*** MSC-WARNING - MSCEWI3084 - PURGE REMOVED FROM DROP TABLE STATEMENT. ***/
/*** MSC-WARNING - MSCEWI3085 - CASCADE CONSTRAINTS MODIFIED INSIDE DROP TABLE STATEMENT TO CASCADE. ***/
DROP TABLE PUBLIC.TEST_TABLE2 CASCADE;

As seen, the code changes. In the new Snowflake code, the PURGE clause is removed and the CONSTRAINTS word is also removed from the CASCADE clause.

Functional Equivalence

Run the following code to check for functional equivalence, bear in mind the only part that is not equivalent is the PURGE clause, which in Oracle removes completely the table from the system and there is no equal for Snowflake. In both cases, the table is dropped even if it's referenced in another table.

Oracle:

CREATE TABLE TEST_TABLE2
    ( col2    INTEGER,
    CONSTRAINT constraint_name PRIMARY KEY (col2));

CREATE TABLE OTHER_TABLE
    ( other_col    INTEGER  REFERENCES TEST_TABLE2 (col2));


DROP TABLE TEST_TABLE2 CASCADE CONSTRAINTS PURGE;

Snowflake:

CREATE OR REPLACE TABLE PUBLIC.TEST_TABLE2 ( col2    INTEGER,
CONSTRAINT constraint_name PRIMARY KEY (col2));

CREATE OR REPLACE TABLE PUBLIC.OTHER_TABLE ( other_col    INTEGER  REFERENCES PUBLIC.TEST_TABLE2 (col2));

/*** MSC-WARNING - MSCEWI3084 - PURGE REMOVED FROM DROP TABLE STATEMENT. ***/
/*** MSC-WARNING - MSCEWI3085 - CASCADE CONSTRAINTS MODIFIED INSIDE DROP TABLE STATEMENT TO CASCADE. ***/
DROP TABLE PUBLIC.TEST_TABLE2 CASCADE;

Related EWIs

1. MSCEWI3084: Purge removed from Drop Table

2. MSCEWI3085: Cascade constraints modified inside Drop Table to cascade