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
I wanted MySQL to automatically generate IDs,
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
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.