Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can a not null constraint be dropped?

Let's say there's a table created as follows:

create table testTable ( colA int not null ) 

How would you drop the not null constraint? I'm looking for something along the lines of

ALTER TABLE testTable ALTER COLUMN colA DROP NOT NULL; 

which is what it would look like if I used PostgreSQL. To my amazement, as far as I've been able to find, the MySQL docs, Google and yes, even Stackoverflow (in spite of dozens or hundreds of NULL-related questions) don't seem to lead towards a single simple SQL statement which will do the job.

like image 421
Tomislav Nakic-Alfirevic Avatar asked Apr 09 '10 13:04

Tomislav Nakic-Alfirevic


People also ask

How do I drop NOT NULL constraint in SQL Plus?

Removing a NOT NULL constraint is pretty easy, no matter the constraint name was provided by system or user, you can just declare the column as NULL at attribute-level to revert the constraint. SQL> alter table countries modify (region_id null); Table altered.

How do you drop a NOT NULL constraint in a snowflake?

To remove a NOT NULL constraint for a column in Snowflake, you use the ALTER TABLE <table_name> ALTER <column_name> DROP command and restate the column definition, adding the NOT NULL attribute.

What is the rule of NOT NULL constraint?

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.


1 Answers

I would try something like this

ALTER TABLE testTable MODIFY COLUMN colA int; 
like image 162
michael.zischka Avatar answered Sep 24 '22 21:09

michael.zischka