COLUMN DEFINITION
The table's schema information
Grammar syntax
column :=
column_name column_schema
column_schema :=
{
simple_type
| STRUCT<field_list>
| ARRAY<array_element_schema>
}
[PRIMARY KEY NOT ENFORCED | REFERENCES table_name(column_name) NOT ENFORCED]
[DEFAULT default_expression]
[NOT NULL]
[OPTIONS(column_option_list)]
simple_type :=
{ data_type | STRING COLLATE collate_specification }
field_list :=
field_name column_schema [, ...]
array_element_schema :=
{ simple_type | STRUCT<field_list> }
[NOT NULL]
For more information, please refer to BigQuery Column Definition.
Data types
Click here for more information about the translation spec for data types. Also see more information about data types in BigQuery here.
Primary Key Not Enforced
Primary Key Not Enforced is fully supported by Snowflake.
Sample Source
CREATE TABLE table1 (
col1 integer PRIMARY KEY NOT ENFORCED
);
References Not Enforced
References Not Enforced is fully supported by Snowflake.
Sample Source
CREATE TABLE table1 (
col1 integer,
col2 integer REFERENCES table_name(column_name) NOT ENFORCED
);
Default default_expression
The default value assigned to the column. For more information, please refer to specify default column values.
Default constraint is fully supported by Snowflake.
Sample Source
CREATE TABLE table1 (
col1 integer,
col2 integer DEFAULT 10
);
Not null
When the NOT NULL
constraint is present for a column or field, the column or field is created with REQUIRED mode. Conversely, when the NOT NULL
constraint is absent, the column or field is created with NULLABLE
mode.
Not null constraint is fully supported by Snowflake.
Sample Source
CREATE TABLE table1 (
col1 integer NOT NULL
);
Options
For more information, please refer to Column Option List.
Options clause is not supported by Snowflake.
Sample Source
CREATE TABLE table1 (
col1 VARCHAR(20) OPTIONS(description="A repeated STRING field")
);
String Collate
Supported grammar in Snowflake with a minor change in the separation of the language_tag and the collation_attribute. In Snowflake you must use hyphen (-) as separator instead of the colon (:).
collation_specification: 'language_tag:collation_attribute'
For more information, please refer to Default collate specification.
String collate is fully supported by Snowflake.
Sample Source
CREATE TABLE table1 (
col1 STRING COLLATE 'und:ci'
);
Related EWIs
MSC-BQ0001: The Create Table OPTIONS clause is not supported in Snowflake.
Last updated
Was this helpful?