CODEWITHSUNDEEP
mysqldatabase-designforeign-keys
Milson
Milson

Reputation: 1575

How to add an auto incremented primary key as a foreign key to existing database tables?

I got three mysql database tables which are properly connected using foreign keys constraints as shown below:

Current Database Schema

But the problem is the foreign key constrained with fields that are not integer and auto increment type instead they are all of varchar type.

I am trying not prevent re-doing the mapping again for these tables which area already correctly mapped but instead want to add a new primary and foreign keys that are integer type and unique. So that all data remain the same in all the tables. Please let me know if any way I can run a script that can auto map existing tables with a new primary and foreign keys.

I tried to add a primary key on base tables

ALTER TABLE videofile ADD id int NOT NULL AUTO_INCREMENT primary key;

This will add a new Primary key to the base table but I want the newly added primary key as FK to other tables based on existing mapped keys and data. Please let me know if there exist any script to customize this need.

Upvotes: 0

Views: 1419

Answers (1)

user13942986
user13942986

Reputation:

This will require rebuilding the tables, so you'll have to rip off the bandaid. As always, test the DDL before making the changes.

  1. Create a new version of the tables with the new key structure:SubjectId/VideoId/<whatever>Id as the primary key, the old primary key as an alternate key (unique constraint). We'll refer to them with the suffix _new.
  2. Lock the tables or take the database offline.
  3. Load the data from Subject into Subject_New first. Then, join Subject_New to Video to get the new key to insert into Video_New.
  4. Repeat the same step for the table dependent on Video.
  5. Disable any dependencies/constraints from the old tables and apply those to the new tables.
  6. Rename the old tables to <table>_old and drop the _new suffix from the tables you just created.
  7. Bring things back online.

Upvotes: 1

Related Questions