Snowflake NOT NULL Constraint and Syntax

  • Post author:
  • Post last modified:January 27, 2020
  • Post category:Snowflake
  • Reading time:5 mins read

Similar to most of the MPP databases, Snowflake cloud database allows you to define constraints. The Snowflake database does not enforce constraints like primary key, foreign key and unique key. But, it does enforce the NOT NULL constraint. In this article, we will check Snowflake NOT NULL constraint, its syntax and usage.

Snowflake NOT NULL Constraint and Syntax

Snowflake NOT NULL Constraint

Constraints other than NOT NULL are created as disabled. Snowflake enforces only NOT NULL. You can create NOT NULL constraint while creating tables in the cloud database.

A Snowflake table can have multiple NOT NULL columns.

Snowflake NOT NULL Constraint Syntax

There are many methods that you can use to add NOT NULL on Snowflake table.

  • Column level NOT NULL – Add NOT NULL constraint during table creation.
  • Alter Table to Add NOT NULL – Use Alter table command to add NOT NULL constraint.

Column level NOT NULL

You can add the NOT NULL to the Snowflake table DDL along with column the data type.

For example, consider below table DDL with a column ID defined as NOT NULL.

CREATE TABLE nn_demo_table 
  ( 
     id      INT NOT NULL, 
     NAME    VARCHAR(10), 
     address VARCHAR(100)
  );

Now, the Snowflake database will allow only non-null values in the ID column. You will end up getting an error if the value is NULL.

Alter Table to Add NOT NULL Constraint

You can also add the NOT NULL constraint to the existing table. The table must be empty in order to SET NOT NULL constraint.

For example, consider following ALTER statement to add NOT NULL with default value.

ALTER TABLE nn_demo_table MODIFY COLUMN ID SET NOT NULL;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
Test Add NOT NULL Constraint

Now, let us try to insert the NULL values into the not null column and check the error.

>INSERT INTO nn_demo_table values (1,'a','abc');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

>INSERT INTO nn_demo_table values (null,'a','abc');
100072 (22000): NULL result in a non-nullable column

As you can see in the above example, you can only insert non-null values. Therefore, Snowflake cloud data warehouse enforces the NOT-NULL constraint.

Related Articles,

Hope this helps 🙂

Tags: ,