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
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,
- Snowflake Unique Key Constraint and Syntax
- Primary Key Constraint in Snowflake and Syntax
- Snowflake Foreign Key Constraint and Syntax
Hope this helps 🙂