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 everything and start fresh, but had to alter the existing table.
So first I added the extra column 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;
crawl_id had to be assigned in order. While this might be possible with a more complicated query I decided that manually increasing the value was faster, especially as the maximum value for
crawl_id was only 10.
UPDATE statements I could finally add the foreign key constraint 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;
Blog - Email - Twitter