Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Redshift - How to remove NOT NULL constraint?

Tags:

Since Redshift does not support ALTER COLUMN, I would like to know if it's possible to remove the NOT NULL constraints from columns in Redshift.

like image 569
shihpeng Avatar asked Apr 09 '15 11:04

shihpeng


People also ask

How do you remove a NOT NULL constraint?

To remove a NOT NULL constraint for a column in MySQL, you use the ALTER TABLE .... MODIFY command and restate the column definition, removing the NOT NULL attribute.

How do I drop a constraint in Redshift?

To drop a constraint, specify the constraint name, not the constraint type. To view table constraint names, run the following query. A clause that removes only the specified constraint. RESTRICT is an option for DROP CONSTRAINT.

How do you change not null constraint to NULL in SQL?

To remove a NOT NULL constraint for a column in SQL Server, you use the ALTER TABLE .... ALTER COLUMN command and restate the column definition.


1 Answers

You cannot alter the table.

There is an alternative approach. You can create a new column with NULL constraint. Copy the values from your old column to this new column and then drop the old column.

Something like this:

ALTER TABLE table1 ADD COLUMN somecolumn (definition as per your reqm);
UPDATE table1 SET somecolumn = oldcolumn;
ALTER TABLE table1 DROP COLUMN oldcolumn;
ALTER TABLE table1 RENAME COLUMN somecolumn TO oldcolumn;
like image 183
Rahul Tripathi Avatar answered Sep 22 '22 13:09

Rahul Tripathi