MySQL Workbench – Unable to Drop Column’s NULL Default

mariadb-10.3MySQLmysql-workbench

Running mariadb 10.3

I have a bunch of columns that were created by default as NULL defaults. I was asked by a third party integrator to remove the NULL defaults, but so far have been unsuccessful.

I first do:

mysql> Describe Tablename;

I see my columns with Null defaults. e.g:

+--------------------------+---------------------+------+-----+---------+-------------------+
| Field                    | Type                | Null | Key | Default | Extra             |
+--------------------------+---------------------+------+-----+---------+-------------------+
| Tech ID                  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment    |
| State                    | tinyint(1) unsigned | NO   |     | 1       |                   |
| Ref ProdID               | int(10) unsigned    | NO   | MUL | NULL    |                   |
| Prenom                   | varchar(50)         | YES  |     | NULL    |                   |

Then I do:

mysql>ALTER TABLE Tablename ALTER COLUMN Prenom DROP DEFAULT;
Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

Describe again shows that the Null default has not been removed from column Prenom. 

+--------------------------+---------------------+------+-----+---------+-------------------+
| Tech ID                  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment    |
| State                    | tinyint(1) unsigned | NO   |     | 1       |                   |
| Ref ProdID               | int(10) unsigned    | NO   | MUL | NULL    |                   |
| Prenom                   | varchar(50)         | YES  |     | NULL    |                   |

Is it even possible to do this?

Best Answer

ALTER TABLE TableName ALTER COLUMN Prenom  DROP DEFAULT

Does remove the default as showed in SHOW CREATE TABLE and attempting to insert without a value results in an error.

It appears DESCRIBE TABLE is using NULL as both a literal, and without a value in its output. Always rely on SHOW CREATE TABLE

select version();

| version()                                   |
| :------------------------------------------ |
| 10.3.11-MariaDB-1:10.3.11+maria~stretch-log |

create table TableName ( Prenom  varchar(50) )

✓

show create table TableName

Table     | Create Table                                                                                               
:-------- | :----------------------------------------------------------------------------------------------------------
TableName | CREATE TABLE `TableName` (`Prenom` varchar(50) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

ALTER TABLE TableName ALTER COLUMN Prenom  DROP DEFAULT

✓

show create table TableName

Table     | Create Table                                                                                  
:-------- | :---------------------------------------------------------------------------------------------
TableName | CREATE TABLE `TableName` (`Prenom` varchar(50)) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into TableName VALUES ()

Field 'Prenom' doesn't have a default value

db<>fiddle here

Related Question