I am currently available as a freelance Senior Perl Programmer. Download my up-to-date resume (PDF)
John Bokma MexIT
freelance Perl programmer

MySQL ALTER TABLE examples

Thursday, August 21, 2008 | 0 comments

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.

MySQL related

Also today

Please post a comment | read 0 comments | RSS feed