Mysql – Altering a column: null to not null

alter-tableMySQL

I'm having to change a column in MYSQL to NULL to NOT NULL, but I get the error excutar the syntax. I want to change only the last column, already performed some research but have not found the solution, which can be simple but I'm new to database and had not realized this until the moment. can anybody help me?

Example held: Quickly change NULL column to NOT NULL

ALTER TABLE glpi_ticketsatisfactions ALTER COLUMN comment BIGINT NOT NULL;

My table: My table

Error:

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BIGINT NOT NULL' at line 1

CREATE TABLE:

CREATE TABLE glpi_ticketsatisfactions 
( id int(11) NOT NULL AUTO_INCREMENT, 
  tickets_id int(11) NOT NULL DEFAULT '0', 
  type int(11) NOT NULL DEFAULT '1', 
  ate_begin datetime DEFAULT NULL, 
  date_answered datetime DEFAULT NULL,
  satisfaction int(11) DEFAULT NULL, 
  comment text COLLATE utf8_unicode_ci, 
  PRIMARY KEY (id), 
  UNIQUE KEY tickets_id (tickets_id) 
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Best Answer

The syntax you used is from a SQL Server example, not from MySQL. It would be good to check the MySQL documentation about ALTER TABLE syntax.

In MySQL, the ALTER COLUMN subclause can only be used for setting or dropping the default value of the column (SET DEFAULT literal or DROP DEFAULT).

You need to use either CHANGE COLUMN (note that column name is doubled as you could use this to change its name):

ALTER TABLE MyTable  
    CHANGE COLUMN comment comment BIGINT NOT NULL;

or MODIFY COLUMN:

ALTER TABLE MyTable 
    MODIFY COLUMN comment BIGINT NOT NULL;

Now, there are 2 more problems, beside the syntax:

  • Seeing the CREATE TABLE, converting a "comment" column from TEXT to BIGINT does not make much sense. I'll assume that you want to keep it TEXT and only make it NOT NULL.

  • There might be NULL values already in the table. If that's the case, the statement will fail with errors. So, you have to first UPDATE those values to some non-null value (say the empty string) and then modify the column:

So our statements become:

UPDATE glpi_ticketsatisfactions 
SET comment = ''
WHERE Comment IS NULL ;

ALTER TABLE glpi_ticketsatisfactions 
    MODIFY COLUMN comment TEXT COLLATE utf8_unicode_ci NOT NULL ;
Related Question