CODEWITHSUNDEEP
mysqlnullconstraints
Tomislav Nakic-Alfirevic
Tomislav Nakic-Alfirevic

Reputation: 10173

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.

Upvotes: 70

Views: 53281

Answers (8)

Loganathan
Loganathan

Reputation: 921

This worked for me in postgres:

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

Upvotes: -1

Chaitanya
Chaitanya

Reputation: 11

ALTER TABLE test.tbl_employee 
CHANGE COLUMN `DepartmentId` `DepartmentId` INT(11) NULL;

Upvotes: 1

Tushar Zagade
Tushar Zagade

Reputation: 469

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.

Upvotes: 0

B-shan
B-shan

Reputation: 404

This works for me

ALTER TABLE table_name
MODIFY COLUMN column_name VARCHAR(25) UNIQUE;

Upvotes: 0

Pratik Kar
Pratik Kar

Reputation: 31

Try

ALTER TABLE testTable MODIFY COLUMN columnA int;

Upvotes: 1

Seva Alekseyev
Seva Alekseyev

Reputation: 61378

In MySQL, nullability is a part of the datatype, not a constraint. So:

ALTER TABLE testTable MODIFY COLUMN colA int null;

Upvotes: 18

Noah Ternullo
Noah Ternullo

Reputation: 677

The syntax was close its actually:

ALTER TABLE testTable CHANGE colA colA int null;

Upvotes: 3

michael.zischka
michael.zischka

Reputation: 1116

I would try something like this

ALTER TABLE testTable MODIFY COLUMN colA int;

Upvotes: 97

Related Questions