remove not null constraint in redshift

How to Remove Not Null Constraint in Redshift

Redshift does not allow you to drop null constraints for columns if you want to allow null values in your table. This is because it does not allow you to modify or alter columns. So we have to take a roundabout approach to drop not null constraint in Redshift. In this article we will look at how to remove not null constraint in Redshift.


How to Remove Not Null Constraint in Redshift

Here are the steps to remove NOT NULL constraint in Redshift. There is no way to change an existing column in Redshift, since it does not support ALTER COLUMN or ALTER TABLE … MODIFY statements. So you will need to create a new column, without NOT NULL constraint, copy the data from old column to new column, and then delete the old column.

Log into Redshift.

Let us say you have a sales(id, order_date, amount) table such that amount column has NOT NULL constraint.

create table sales (   
   id int primary key,   
   order_date date,
   amount int not null
);

Also Read : How to Create Table in Redshift

Since we need to remove NOT NULL constraint from amount column, we will create a new column new_amount in sales table using ALTER TABLE statement, without a NOT NULL constraint.

ALTER TABLE sales ADD COLUMN new_amount INT;

Copy data from amount to new_amount columns using UPDATE statement.

UPDATE sales SET new_amount = amount;

Drop amount column.

ALTER TABLE sales DROP COLUMN new_amount;

Also Read : How to Insert Data into Redshift Table

Rename new_amount column to amount

ALTER TABLE sales RENAME COLUMN new_amount TO amount;

As you can see, since Redshift does not support ALTER COLUMN statement, we need to use a roundabout method to remove NOT NULL constraint in Redshift.

Leave a Reply

Your email address will not be published. Required fields are marked *