Alter Table

This section shows you the translations related to ALTER TABLE.

1. Description

Use the ALTER TABLE statement to alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition. For object tables or relational tables with object columns, use ALTER TABLE to convert the table to the latest definition of its referenced type after the type has been altered (Oracle documentation).

Oracle syntax

ALTER TABLE [ schema. ] table
  [ alter_table_properties
  | column_clauses
  | constraint_clauses
  | alter_table_partitioning
  | alter_external_table
  | move_table_clause
  ]
  [ enable_disable_clause
  | { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS }
  ] ...
  ;

To review Snowflake syntax, review the following documentation.

2. Sample Source Patterns

2.1. Alter table with clauses

memoptimize_read_clause and memoptimize_read_clause are not applicable in Snowflake so are being removed.

Oracle

Query

ALTER TABLE SOMESCHEMA.SOMENAME 
MEMOPTIMIZE FOR READ 
MEMOPTIMIZE FOR WRITE
 ADD (SOMECOLUMN NUMBER , SOMEOTHERCOLUMN VARCHAR(23)) 
 (PARTITION PT NESTED TABLE COLUMN_VALUE STORE AS SNAME
 ( SUBPARTITION SPART NESTED TABLE COLUMN_VALUE STORE AS SNAME))
ENABLE TABLE LOCK;

Snowflake

Query

ALTER TABLE SOMESCHEMA.SOMENAME
ADD (SOMECOLUMN NUMBER(38, 18), SOMEOTHERCOLUMN VARCHAR(23));

Only some column_clauses and constraint_clauses are applicable in Snowflake. In Oracle alter table allows modifying properties from partitions created but in Snowflake, these actions are not required

2.2. Alter table with not supported cases

Oracle

Query

ALTER TABLE SOMENAME MODIFY COLUMN SCOLUMN NOT SUBSTITUTABLE AT ALL LEVELS FORCE;

ALTER TABLE SOMENAME MODIFY(SCOLUMN VISIBLE,SCOLUMN INVISIBLE);

ALTER TABLE SOMENAME MODIFY VARRAY VARRAYITEM (
STORAGE(PCTINCREASE 10));

Snowflake

Query

----** MSC-ERROR - MSCEWI3046 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE **
--ALTER TABLE SOMENAME
--MODIFY COLUMN SCOLUMN NOT SUBSTITUTABLE AT ALL LEVELS FORCE
                                                           ;

----** MSC-ERROR - MSCEWI3046 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE **
--ALTER TABLE SOMENAME
--MODIFY(SCOLUMN VISIBLE,SCOLUMN INVISIBLE)
                                         ;

----** MSC-ERROR - MSCEWI3046 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE **

--ALTER TABLE SOMENAME
--MODIFY VARRAY VARRAYITEM (
--STORAGE(PCTINCREASE 10))
                        ;

2.3. ADD CONSTRAINT action

The ADD CONSTRAINT action has an equivalent in Snowflake, but it only one constraint can be added per ALTER TABLE statement, so it will be commented when the statement contains two or more constraints.

Oracle

Query

-- MULTIPLE CONSTRAINT ADDITION SCENARIO
ALTER TABLE TABLE1 ADD (
CONSTRAINT TABLE1_PK
PRIMARY KEY
(ID)
ENABLE VALIDATE,
CONSTRAINT TABLE1_FK foreign key(ID2)
references TABLE2 (ID) ON DELETE CASCADE);

-- ONLY ONE CONSTRAINT ADDITION SCENARIO
ALTER TABLE TABLE1 ADD (
CONSTRAINT TABLE1_FK foreign key(ID2)
references TABLE2 (ID) ON DELETE CASCADE);

Snowflake

Query

---- MULTIPLE CONSTRAINT ADDITION SCENARIO
----** MSC-ERROR - MSCEWI3067 - MULTIPLE CONSTRAINT DEFINITION IN A SINGLE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE. **
--ALTER TABLE TABLE1
--ADD (
--CONSTRAINT TABLE1_PK
--PRIMARY KEY
--(ID)
----** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. CONSTRAINT STATE ENABLE DISABLE **
--ENABLE
--       --** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. CONSTRAINT STATE VALIDATE NO VALIDATE **
--       VALIDATE,
--CONSTRAINT TABLE1_FK foreign key(ID2)
--references TABLE2 (ID) ON DELETE CASCADE)
                                         ;

-- ONLY ONE CONSTRAINT ADDITION SCENARIO
ALTER TABLE TABLE1
ADD
CONSTRAINT TABLE1_FK foreign key(ID2)
references TABLE2 (ID) ON DELETE CASCADE;

3. Known Issues

1. Some properties on the tables may be adapted to or not applicable.

4. Related EWIs

MSCEWI1002: Removed next statement, not applicable in SnowFlake.

MSCEWI3046: Alter Table syntax is not applicable in Snowflake.