How to use Redshift NOT NULL Constraint? Its Syntax

In my other Redshift related article, we have discussed how to define a primary key, Unique key and Foreign key constraint in Amazon Redshift. In this article, we will check how to use Redshift NOT NULL constraint with its syntax and examples.

Redshift NOT NULL Constraint

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.

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 columns while creating table.
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 mention NOT NULL to the table columns along with data types. This is a very common method and almost all databases support this.

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. The Database will throw an error if you try to insert NULL values.

For example, following insert statement demonstrates the not null constraints in Amazon 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]
  -----------------------------------------------

From the error message you can conclude that, 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 use the ALTER TABLE command to add a new column with a NOT NULL constraint to an existing table. Please note that, you should define default value for the new column.

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