DEFAULT

Description

When DEFAULT clause is in the ALTER statement, Snowconvert will comment out the entire statement, since it is not supported.

The only functional scenario happens when the table definition is on the same file, in this way the default is added in the column definition.

Sample Source Patterns

SQL Server

CREATE TABLE table1
(
  col1 integer not null,
  col2 varchar collate Latin1_General_CS,
  col3 date not null
)

ALTER TABLE table1
ADD CONSTRAINT col1_constraint DEFAULT 50 FOR col1;

ALTER TABLE table1
ADD CONSTRAINT col2_constraint DEFAULT 'hello world' FOR col2;

ALTER TABLE table1
ADD CONSTRAINT col3_constraint DEFAULT getdate() FOR col3;

Snowflake

CREATE OR REPLACE TABLE PUBLIC.table1 (
  col1 INTEGER not null DEFAULT 50,
  col2 VARCHAR COLLATE 'EN-CS' DEFAULT 'hello world',
  col3 DATE not null
-- ** MSC-ERROR - MSCEWI1088 - EXPRESSIONS LIKE FUNCTION CALLS, VARIABLES, OR NAMED CONSTANTS ARE NOT SUPPORTED ON DEFAULT OPTION IN SNOWFLAKE **
--                     DEFAULT CURRENT_TIMESTAMP() :: TIMESTAMP
);

-- ** MSC-ERROR - MSCEWI4058 - ONE OR MORE OF THE TABLE ELEMENT PARTS ARE NOT SUPPORTED IN SNOWFLAKE: DEFAULT TABLE CONSTRAINT **
--ALTER TABLE PUBLIC.table1
--ADD CONSTRAINT col1_constraint DEFAULT 50 FOR col1
                                                  ;

-- ** MSC-ERROR - MSCEWI4058 - ONE OR MORE OF THE TABLE ELEMENT PARTS ARE NOT SUPPORTED IN SNOWFLAKE: DEFAULT TABLE CONSTRAINT **
--ALTER TABLE PUBLIC.table1
--ADD CONSTRAINT col2_constraint DEFAULT 'hello world' FOR col2
                                                             ;

-- ** MSC-ERROR - MSCEWI4058 - ONE OR MORE OF THE TABLE ELEMENT PARTS ARE NOT SUPPORTED IN SNOWFLAKE: DEFAULT TABLE CONSTRAINT **
--ALTER TABLE PUBLIC.table1
--ADD CONSTRAINT col3_constraint DEFAULT getdate() FOR col3
                                                         ;

Known Issues

1. ALTER TABLE DEFAULT clause is not supported in Snowflake.

The entire ALTER TABLE DEFAULT clause is commented out, since it is not supported in Snowflake.

Related EWIs

• MSCEWI4058: One or more table elements parts are not supported in Snowflake.

• MSCEWI1088: Expressions like function calls, variables, or named constants are not allowed on default option in Snowflake