John Bokma

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 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;

Each 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.

After ten 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;

ALTER TABLE Related

home - blog - email

I am a freelance Perl programmer for hire, download my up-to-date resume (PDF).