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
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.
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;