Adding an extra column and a foreign key to a MySQL database table
August 1, 2016
A database table, part of a project I am currently working on, required an additional column with a foreign key. Because the database has already valuable data I couldn't just drop all tables and start fresh, but had to alter the existing table.
So first I added the extra column to the table using:
ALTER TABLE url_loc
ADD COLUMN first_crawl_id INT UNSIGNED NOT NULL
AFTER value;
Before I could assign the foreign key I had to set first_crawl_id to
the correct value for each row already in the database. The required
value depends on several tables, hence I had to use a multiple-table
UPDATE as follows:
UPDATE url_loc AS ul, url_loc_sitemap_file AS ulsf, sitemap_file AS sf
SET ul.first_crawl_id = sf.crawl_id
WHERE ulsf.sitemap_file_id = sf.id
AND ul.id = ulsf.url_loc_id
AND sf.crawl_id = 1;
Each crawl_id had to be assigned in order. While this might be
possible with a single, more complicated query I decided that manually
increasing the value was faster then trying to figure out such a
query, especially as the maximum value for crawl_id was only 10.
After ten UPDATE statements I could finally add the foreign key
constraint to the table as follows:
ALTER TABLE url_loc
ADD FOREIGN KEY first_crawl_id_fk (first_crawl_id )
REFERENCES crawl (id)
ON DELETE CASCADE
ON UPDATE CASCADE;