How can I change my existing column in MariaDB to Not Null?

Question

Message : 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 ''country_of_residence_id' INTEGER NOT NULL' Statement : ALTER TABLE address ALTER COLUMN 'country_of_residence_id' INTEGER NOT NULL

In my table 'address' I want to set an already existing column 'country_of_residence_id' to NOT NULL.

I tried it this way:

ALTER TABLE address
ALTER COLUMN 'country_of_residence_id' INTEGER NOT NULL;

My IDE underlines INTEGER and says: DROP or SET expected, got "INTEGER"

When I add SET before INTEGER it doesn't work either.

Lecythus

Answer 1

I found it here: https://mariadb.com/kb/en/mariadb/alter-table/

alter table address modify country_of_residence_id bigint unsigned NOT NULL;

Lecythus

Answer 2

First of all, make all existing NULL values of rows disappear:

UPDATE [Table_Name] SET [Column_Name]=0 WHERE [Column_Name] IS NULL;

Then, update(alter) the table definition to reject NULLs:

ALTER TABLE [Table_Name] MODIFY [Column_Name] BIGINT UNSIGNED NOT NULL;

Nutcracker

The update null values line is key if you already have values in table, otherwise nothing changes and you get something like: ERROR 1265 (01000): Data truncated for column 'confirmed' at row 1 I think this should be accepted answer for this reason. – Galantine