My HealthPassportYour health research identity. Your choice.
For Database Administrators
Database administration — reference note
Adding SHA-256 keys to an existing SLK581-enabled database
Instructions for database managers on generating a one-way hash of a Statistical Linkage Key for existing records, and keeping it in sync automatically for every record added or changed from now on.
Most existing databases that record an SLK581 field can be upgraded to also store a SHA-256 one-way hash of that key — and to keep generating it automatically, with no ongoing maintenance. No new infrastructure is required, this is achieved through simple SQL programming that most database managers can run directly against their existing tables.
The queries below add a new field called SHA256 as a generated column. A generated column is calculated by the database itself rather than written to directly, so a single statement does two jobs at once: it computes the hash for every row already in the table, and it recalculates the hash automatically whenever an SLK581 value is later inserted or modified. No separate backfill step and no trigger are needed.
In every case, the SLK581 value is capitalised and stripped of spaces before it is hashed. Normalising case and spacing first ensures that the same person produces the same hash regardless of which source system originally recorded their record.
Assumption: the code below assumes the existing field is named SLK581. If your table uses a different column name, replace it before running these queries. You will also need to replace your_table with the name of the table that contains the SLK581 field.
PostgreSQL
Requires the pgcrypto extension, enabled once per database. The STORED column backfills existing rows immediately and recalculates on every future write to SLK581.
SQL · PostgreSQL
-- Enable the hashing extension (once per database)CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Add the field as a generated column-- Backfills existing rows and recalculates on every future insert/updateALTER TABLE your_table
ADD COLUMN SHA256 TEXTGENERATED ALWAYS AS (
encode(digest(UPPER(REPLACE(SLK581, ' ', '')), 'sha256'), 'hex')
) STORED;
MySQL / MariaDB
No extension required — SHA2() is built in. STORED backfills existing rows immediately and recalculates on every future write to SLK581.
SQL · MySQL / MariaDB
-- Add the field as a generated column-- Backfills existing rows and recalculates on every future insert/updateALTER TABLE your_table
ADD COLUMN SHA256 CHAR(64)
GENERATED ALWAYS AS (
SHA2(UPPER(REPLACE(SLK581, ' ', '')), 256)
) STORED;
SQL Server
HASHBYTES returns binary, so the result is converted to a readable hex string. PERSISTED backfills existing rows immediately and recalculates on every future write to SLK581.
SQL · SQL Server
-- Add the field as a computed column-- Backfills existing rows and recalculates on every future insert/updateALTER TABLE your_table
ADD SHA256 AS (
CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', UPPER(REPLACE(SLK581, ' ', ''))), 2)
) PERSISTED;
Before running this at scale
One statement, not two. Adding SHA256 as a generated column both backfills every existing row and keeps future inserts and updates in sync. There is no separate step to remember, and no risk of the hash drifting out of date.
SHA256 can't be hand-edited. Because the database calculates it, you can't write to SHA256 directly — any attempt to do so will be rejected.
NULL values. If SLK581 can be NULL, SHA256 will be NULL for that row rather than hashing an empty string.
One-way only. A SHA-256 hash cannot be reversed back into the original SLK581. Keep the source SLK581 field if you still need to regenerate or re-verify hashes in future.