How to add Primary Key to an existing table?

I have created a table without defining the primary key. When I added values in the table I omitted the field, so all the values for that field was set to NULL. When I tried adding primary key to it later by using the following command:

ALTER TABLE teachers ADD CONSTRAINT pk_teachers_id PRIMARY KEY(id);

…it throws me an error saying the column has NULL values. So I use the following command to set all the values to 1:

UPDATE teachers SET id = 1 WHERE id IS NULL;

This worked and set all the values to 1, however, I still cannot add the primary key to this field. I am getting error 1064.

I am using MySQL database.

Answer

Recommended solution – remove and re-create as primary key:

ALTER TABLE teachers 
    DROP COLUMN id,
    ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=26c8d719ff1e3a77297962029a587b65