How can a not null constraint be dropped?

mysql null constraints
Tomislav Nakic-Alfirevic picture Tomislav Nakic-Alfirevic · Apr 9, 2010 · Viewed 38.8k times · Source

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.

Answer

michael.zischka picture michael.zischka · Apr 9, 2010

I would try something like this

ALTER TABLE testTable MODIFY COLUMN colA int;

Related questions

MySQL: selecting rows where a column is null

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of …

Read More
MySQL and PHP - insert NULL rather than empty string

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng). Right now, if these values are not entered I pass along an empty string as a value. How do …

Read More
Checking for an empty field with MySQL

I've wrote a query to check for users with certain criteria, one being they have an email address. Our site will allow a user to have or not have an email address. $aUsers=$this->readToArray(' SELECT `userID` FROM `…

Read More