0

I am currently rebuilding a database which is used to store patient records. In the current database, the primary key for a patient is their name and date of birth, (a single column, ie "John Smith 1970-01-01", it is not composite). This is also a foreign key in many other tables to reference the patients table. I am planning to replace this key with an auto-generated integer key (since there will obviously be duplicate keys one day under the current system). How can I add a new primary key to this table and add appropriate foreign keys on all the other tables? Keep in mind that there is already a very large amount of data (~500,000 records) and these data references cannot be broken.

Thanks!

Paul
  • 256
  • 4
  • 18

2 Answers2

1

If up to me..

  1. Add a new future-PK column as a non-null unique index (it must be a KEY, but not necessarily the PK) with auto_increment.

  2. Add the appropriate new-FK columns to all the related tables, these should be initially nullable.

    1. Set the new-FK value to the appropriate future-PK value based on the current-PK/FK relationships. Use an "UPDATE .. JOIN" for this step.

    2. Enable the Referential Integrity Constraints (DRI) on the relevant tables. It only needs to be KEY/FK, not PK/FK, which is why the future-PK can be used. Every existing DRI constraint using the current-PK should likely be updated during this step.

    3. Remove the new-FK column nullability based on modeling requirements.

    4. Remove any residue old-FK columns as they are now redundant data.

  3. Switch the old-PK and the new/future-PK (this can be done in one command and may take awhile to physically reorganize all the rows). Remove the old PK column as applicable, or perhaps simply remove the KEY status.

I would also offline the database during the process, review and test the process (use a testing database for dry-runs), and maintain backups.

The Data-Access Layer and any Views/etc will also need to be updated. These should be done at the same time, again through a review and testing process.

Also, even when adding an auto-increment PK, the table should generally still have an appropriate covering natural key enforced with unique constraints.

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
0

I solved the problem using the following method:

1- Assigned added a new primary key to the patients table and assigned unique values to all existing records

2- Created materialized views (without triggers) for each of the referencing tables that included all fields in the referencing table as well as the newly created id field in the patients table (via a join).

3- Deleted the source referencing tables

4- Renamed the materialized views to the names of the original source tables

The materialized views are now the dependent tables.

A reference for materialized views: http://www.fromdual.com/mysql-materialized-views

Paul
  • 256
  • 4
  • 18
  • Nit: *No* [Materialized Views](http://en.wikipedia.org/wiki/Materialized_view) were created because MySQL does *not support* materialized views. What you did was to create *new tables*, *copy the data over* (re-establishing relationships), and then *drop the old tables*. – user2864740 Mar 27 '14 at 03:07
  • @user2864740 While MySQL does not natively provide support for materialized views, they can be implemented by copy data in the method that you described and optionally adding triggers to refresh the table appropriately. I described my process as using materialized views for the sake of simplicity. http://www.fromdual.com/mysql-materialized-views – Paul Mar 27 '14 at 15:39
  • Uh, no, that is use *tables* to [simulate](http://dictionary.reference.com/browse/simulate) - "to assume or have the appearance or characteristics of" - materialized views (fsvo) as the above step. Again, tables and materialized views are different things. Furthermore, the "MVs" weren't event used in this case for anything but new tables. – user2864740 Mar 27 '14 at 17:03