Due to new requirements for a project I am working on I had to alter an existing MySQL table: the primary key constraint had to be removed (and later added to a new column), a new column named ID had to be added as the first column with a primary key constraint. Finally, the column that used to have the PK constraint required a UNIQUE constraint now.
In small clear steps now: first, I removed the PK constraint as follows:
ALTER TABLE Example DROP PRIMARY KEY;Note: in the above and following examples I've replaced the actual name of the table with Example.
Next, an ID column had to be added of type INTEGER. Since
I wanted MySQL to automatically generate IDs, AUTO_INCREMENT
and NOT NULL were specified as well. Moreover the new column
required a PK constraint. MySQL's ALTER TABLE syntax allows for multiple
alter specifications separated by a comma, shown in this example:
ALTER TABLE Example ADD ID INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (ID);Finally the column that had the PK constraint, URL, got now a UNIQUE constraint:
ALTER TABLE Example ADD UNIQUE (URL);
After I had made those changes I verified the changes using DESCRIBE Example
in the mysql client program. Also, as I expected, SELECT * FROM Example
showed that MySQL had assigned a unique number to ID for each row that was already in Example.