Redshift NOT NULL Constraint, Syntax and Examples

Post author:Vithal S
Post last modified:April 14, 2021
Post category:Redshift
Reading time:5 mins read

Similar to most of the MPP databases such as Snowflake, the Amazon Redshift database allows you to define constraints. The Redshift 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 Redshift NOT NULL constraint, its syntax and usage.

Redshift NOT NULL Constraint

Constraints other than NOT NULL are created as disabled. Amazon Redshift enforces only NOT NULL. You can create NOT NULL constraint while creating tables.

Redshift NOT NULL Constraint Syntax

There are different methods that you can use to add NOT NULL on Redshift table.

Column level NOT NULL – Add NOT NULL constraint during table creation.
Alter Table to Add NOT NULL Column – You can also use alter table command to add NOT NULL constraint after table is created.

Column level NOT NULL

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

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

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

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

For example, following insert statement demonstrates the not null constraints in Redshift.

# insert into notnull_demo_table values(1,'abc','');
INSERT 0 1

# insert into notnull_demo_table values(null,'abc','');
ERROR:  Cannot insert a NULL value into column id
DETAIL:
  -----------------------------------------------
  error:  Cannot insert a NULL value into column id
  code:      8007
  context:   query execution
  query:     2183
  location:  column:1
  process:   query0_127_2183 [pid=22559]
  -----------------------------------------------

As you can see in the above example, you can only insert non-null values. Therefore, Redshift database enforces the NOT-NULL constraint.

Alter Table to Add NOT NULL Column

You can also add new column with NOT NULL constraint to the existing table. You should specify the default value.

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

# ALTER TABLE nn_demo_table ADD id2 int NOT NULL default 0 ;
ALTER TABLE