Migration of secondary tables to use primary keys

Some customers have asked how to migrate secondary tables to use primary keys instead of unique foreign keys. This guide outlines the steps for performing that migration. Due to the potentially high impact of reindexing secondary tables, plan the migration carefully. This migration is optional.

danger

This migration is not mandatory. If you do not want to migrate your secondary tables to use primary keys, you can continue to use the unique foreign keys as they are. However, if you want to take advantage of the benefits of using primary keys, you can follow the steps outlined in this migration guide to migrate your secondary tables to use primary keys.

Considerations

When migrating secondary tables to use primary keys, there are several important considerations to keep in mind:

• For large amounts of data, reindexing the secondary tables can take a long time. It is recommended to perform this operation during off-peak hours or in a maintenance window to minimize the impact on users.
• Ensure that you have a backup of your data before starting the migration process. This will allow you to restore your data in case anything goes wrong during the migration.
• Test the migration process in a staging environment before applying it to your production environment. This will help you identify any potential issues and ensure that the migration process runs smoothly.

TIDMA_LOGIN_ID_GENERATION table

For this table, we added the migration in version 7.45 because it is a small table (it contains one line per client).

MariaDB

In MariaDB the indexes could not be reused for the newly declared primary key. The migration will drop the existing indexes and create a new primary key index. This operation can take a long time for large tables, so it is recommended to perform this operation during off-peak hours or in a maintenance window.

ALTER TABLE tidma_oath DROP FOREIGN KEY  fk_oath_credid;
DROP INDEX cidma_uq_oath_cred_id ON tidma_oath;
ALTER TABLE tidma_oath ADD CONSTRAINT PRIMARY KEY (credential_id);
ALTER TABLE tidma_oath ADD CONSTRAINT fk_oath_credid FOREIGN KEY (credential_id) REFERENCES tidma_credential (CREDENTIAL_ID);

ALTER TABLE tidma_fido_uaf DROP FOREIGN KEY fk_fidouaf_credid;
ALTER TABLE tidma_fido_uaf ADD CONSTRAINT PRIMARY KEY (credential_id);
ALTER TABLE tidma_fido_uaf ADD CONSTRAINT fk_fidouaf_credid FOREIGN KEY (credential_id) REFERENCES tidma_credential (CREDENTIAL_ID);

ALTER TABLE tidma_saml_federation DROP FOREIGN KEY fk_samlfederation_credid;
ALTER TABLE tidma_saml_federation ADD CONSTRAINT PRIMARY KEY (credential_id);
ALTER TABLE tidma_saml_federation ADD CONSTRAINT fk_samlfederation_credid FOREIGN KEY (credential_id) REFERENCES tidma_credential (CREDENTIAL_ID);

DROP INDEX cidma_uq_certinfo_cred_id ON tidma_cert_info;
ALTER TABLE tidma_cert_info ADD CONSTRAINT PRIMARY KEY (credential_id);
ALTER TABLE tidma_cert_info ADD CONSTRAINT fk_certinfo_credid FOREIGN KEY (credential_id) REFERENCES tidma_credential (CREDENTIAL_ID);

drop index cidma_uq_mobilesign_cred_id on tidma_mobile_signature;
ALTER TABLE tidma_mobile_signature ADD CONSTRAINT PRIMARY KEY (credential_id);
ALTER TABLE tidma_mobile_signature ADD CONSTRAINT fk_mobilesign_credid FOREIGN KEY (credential_id) REFERENCES tidma_credential (CREDENTIAL_ID);

Oracle

In Oracle, the indexes can be reused for the newly declared primary key. The migration reuses the existing indexes instead of dropping and recreating them.

ALTER TABLE tidma_oath DROP CONSTRAINT cidma_uq_oath_cred_id KEEP INDEX;
ALTER TABLE tidma_oath ADD CONSTRAINT tidma_oath_credential_pk PRIMARY KEY (credential_id) USING INDEX I_OATH_CREDID;

ALTER TABLE tidma_mobile_signature DROP CONSTRAINT CIDMA_UQ_MOBILESIGN_CRED_ID KEEP INDEX;
ALTER TABLE tidma_mobile_signature ADD CONSTRAINT tidma_mobile_signature_pk PRIMARY KEY (credential_id) USING INDEX I_MOBILESIGN_CREDID;

ALTER TABLE tidma_saml_federation DROP CONSTRAINT CIDMA_UQ_SAML_CRED_ID KEEP INDEX;
ALTER TABLE tidma_saml_federation ADD CONSTRAINT tidma_saml_federation_pk PRIMARY KEY (credential_id) USING INDEX I_SAMLFEDERATION_CREDID;

ALTER TABLE tidma_cert_info DROP CONSTRAINT CIDMA_UQ_CERTINFO_CRED_ID KEEP INDEX;
ALTER TABLE tidma_cert_info ADD CONSTRAINT tidma_cert_info_pk PRIMARY KEY (credential_id) USING INDEX I_CERTINFO_CREDID;

ALTER TABLE tidma_fido_uaf DROP CONSTRAINT FK_FIDOUAF_CREDID KEEP INDEX;
ALTER TABLE tidma_fido_uaf ADD CONSTRAINT tidma_fido_uaf_pk PRIMARY KEY (credential_id) USING INDEX I_FIDOUAF_CREDID;

CREATE INDEX I_FIDO2_CREDID ON tidma_fido2 (credential_id);
ALTER TABLE tidma_fido2 ADD CONSTRAINT tidma_fido2_pk PRIMARY KEY (credential_id) USING INDEX I_FIDO2_CREDID;

PostgreSQL

Since we used anonymous indexes in PostgreSQL, the indexes could not be reused for the newly declared primary key. The migration will drop the existing indexes and create a new primary key index. This operation can take a long time for large tables, so it is recommended to perform this operation during off-peak hours or in a maintenance window.

ALTER TABLE tidma_oath DROP CONSTRAINT cidma_uq_oath_cred_id, ADD PRIMARY KEY (credential_id);

ALTER TABLE tidma_mobile_signature DROP CONSTRAINT cidma_uq_mobilesign_cred_id, ADD PRIMARY KEY (credential_id);

ALTER TABLE tidma_saml_federation DROP CONSTRAINT cidma_uq_saml_cred_id, ADD PRIMARY KEY (credential_id);

ALTER TABLE tidma_cert_info DROP CONSTRAINT cidma_uq_certinfo_cred_id, ADD  PRIMARY KEY (credential_id);

ALTER TABLE tidma_fido_uaf ADD  PRIMARY KEY (credential_id);