Search code examples
mysqlsqlsyntax-errorddlalter-table

SQL error #1064. Alter table with default values

I tried to do an ALTER TABLE to add some columns with a default value. Why I do I have this error?

I tried with:

column_name varchar(255) NOT NULL DEFAULT '0',

and also with

column_name varchar(255) NOT NULL DEFAULT 0,

but it didn't work in both.

ALTER TABLE utenti
ADD
missileBalistico varchar(255) NOT NULL DEFAULT 1,
missileBalisticoPotenza varchar(255) NOT NULL DEFAULT 60,
...,
aiuti varchar(255) NOT NULL DEFAULT 0;

It gaves me 31 same errors:

Unrecognized alter operation. (near "," at position ...)

And MySQL said:

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 'varchar(255) NOT NULL DEFAULT 60,
basi varchar(255) NOT NULL DEFAULT 1,
vitaBa' at line 4
Solution

  • You need to use several add clauses:

    ALTER TABLE utenti
ADD missileBalistico varchar(255) NOT NULL DEFAULT 1,
ADD missileBalisticoPotenza varchar(255) NOT NULL DEFAULT 60,
-- Other add clauses...
ADD aiuti varchar(255) NOT NULL DEFAULT 0;