Drop Table

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

Some parts in the output code are omitted for clarity reasons.

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:

IN -> Oracle_01.sql

DROP TABLE TEST_TABLE1;

Transformed Code:

OUT -> Oracle_01.sql

DROP TABLE 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:

IN -> Oracle_02.sql

DROP TABLE TEST_TABLE1 PURGE;

Transformed Code:

OUT -> Oracle_02.sql

DROP TABLE TEST_TABLE1;

Example 3:

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

Input Code:

IN -> Oracle_03.sql

DROP TABLE TEST_TABLE1 CASCADE CONSTRAINTS;

Transformed Code:

OUT -> Oracle_03.sql

DROP TABLE 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:

IN -> Oracle_04.sql

DROP TABLE TEST_TABLE1 CASCADE CONSTRAINTS PURGE;

Transformed Code:

OUT -> Oracle_04.sql

DROP TABLE TEST_TABLE1 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:

IN -> Oracle_05.sql

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:

OUT -> Oracle_05.sql

CREATE OR REPLACE TABLE TEST_TABLE2 (
       col2 INTEGER,
       CONSTRAINT constraint_name PRIMARY KEY (col2)
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

   CREATE OR REPLACE TABLE OTHER_TABLE (
          other_col INTEGER REFERENCES TEST_TABLE2 (col2)
      )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

   DROP TABLE TEST_TABLE2 CASCADE;

Related EWIs

No related EWIs.