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.

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;

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;