CODEWITHSUNDEEP
sqldatabasesnowflake-cloud-data-platform
Omkar More
Omkar More

Reputation: 7

How to remove not null constraints for multiple columns at a time in snowflake DB

I am not able to drop/ remove not null constraint in snowflake DB

Following things I have tried already...

ALTER TABLE tablename
alter
colname datatype DROP NOT NULL,

ALTER TABLE tablename
alter
colname datatype DROP CONSTRAINT NOT NULL,

ALTER TABLE tablename
modify column
colname datatype DROP CONSTRAINT NOT NULL,

ALTER TABLE tablename
modify column
colname datatype DROP NOT NULL,

ALTER TABLE tablename
DROP CONSTRAINT NOT NULL colname

Upvotes: 0

Views: 1627

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Removing NOT NULL property from multiple columns at once:

For:

CREATE OR REPLACE TABLE tab(col1 INT NOT NULL,
                            col2 INT NOT NULL,
                            col3 INT NOT NULL);

Alter statement:

ALTER TABLE tab
ALTER COLUMN col1 DROP NOT NULL
     ,COLUMN col2 DROP NOT NULL
     ,COLUMN col3 DROP NOT NULL;

Upvotes: 1

Tejaswa
Tejaswa

Reputation: 525

Refer to the documentation, there is an example of what you want to do

alter table t1 alter column c1 drop not null;

https://docs.snowflake.com/en/sql-reference/sql/alter-table-column.html#examples

Upvotes: 0

Alexander Klimenko
Alexander Klimenko

Reputation: 1685

Datatype is not needed for alter column.

alter table tablename modify column colname DROP NOT NULL;

Doc

Upvotes: 0

Related Questions