How can a not null constraint be dropped?
MysqlNullConstraintsMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
I would try something like this
ALTER TABLE testTable MODIFY COLUMN colA int;
Solution 2 - Mysql
In MySQL, nullability is a part of the datatype, not a constraint. So:
ALTER TABLE testTable MODIFY COLUMN colA int null;
Solution 3 - Mysql
The syntax was close its actually:
ALTER TABLE testTable CHANGE colA colA int null;
Solution 4 - Mysql
Try
ALTER TABLE testTable MODIFY COLUMN columnA int;
Solution 5 - Mysql
ALTER TABLE test.tbl_employee
CHANGE COLUMN `DepartmentId` `DepartmentId` INT(11) NULL;
Solution 6 - Mysql
This works for me
ALTER TABLE table_name
MODIFY COLUMN column_name VARCHAR(25) UNIQUE;
Solution 7 - Mysql
When you modify table constraint or datatype in Mysql workbench then it shows the code it is going to execute to complete the request. And this is the query I got from that box.
ALTER TABLE `table_name`.`column_name` CHANGE COLUMN `column_name` `colunm_name`datatype NULL ;
But the catch here is that you can't have the primary key as null you have to set it unique instead.
Solution 8 - Mysql
This worked for me in postgres:
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;