2021 – Ensuring Uniqueness Part 1

The next feature for the API’s is maintaining unique patient names. This is best done in the database because it is the lowest level of interaction (and defense) for the application. In our case two unique indexes are required – one for the JSON data and one for the relational data:

  • The patient_name column in the patient table
  • The pname key at the top of the JSON structure in the patient_data column in the json_patient table

Here is the existing data for those values.

-- Relational Data
SELECT patient_name
  FROM patient;
PATIENT_NAME
------------
Patient 1

-- JSON Data
SELECT JSON_VALUE(patient_data,'$.pname')
  FROM json_patient;
JSON_VALUE(PATIENT_DATA,'$.PNAME')
----------------------------------
Patient 1

For the relational table a unique constraint can be declared as shown below. The constraint is enforced by an underlying unique index. For the JSON data just create the index directly.

-- define unique constraint for the relational table
-- an underlying unique index enforces the constraint
ALTER TABLE patient
ADD CONSTRAINT patient_pk
UNIQUE ( patient_name );

-- for the JSON data just create the index directly
CREATE UNIQUE INDEX j ON json_patient ( JSON_VALUE(patient_data,'$.pname') );

Now test the constraint/index using SQL.

-- try to insert Patient 1 into the relational table
INSERT INTO patient(patient_id,
                     patient_name)
VALUES(22,
       'Patient 1');
-- Raises ORA-00001: unique constraint (ME.PATIENT_PK) violated

-- try to insert Patient 1 into the JSON table
INSERT INTO json_patient
VALUES('{"pname":"Patient 1"}');
-- Raises ORA-00001: unique constraint (ME.J) violated

Both failures raise the same error (ORA-00001) which translates to the PL/SQL exception DUP_VAL_ON_INDEX. Thus we need to modify the add patient procedures to handle it.

Here is the relational version of the procedure.

PROCEDURE add_patient ( p_name VARCHAR2,
                         p_os   NUMBER,
                         p_mrn  VARCHAR2 ) AS
BEGIN
  INSERT INTO patient(patient_id,
                      patient_name)
  VALUES(patient_seq.nextval,
         p_name);
  INSERT INTO patient_mrn(patient_id,
                          ordering_system,
                          patient_mrn)
  VALUES(patient_seq.currval,
         p_os,
         p_mrn);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- raise uniqueness errors out to the calling program
    RAISE;
  WHEN OTHERS THEN NULL;
END;

And an example call showing the exception being raised.

BEGIN
  patient_pkg.add_patient('Patient 1',1,'MRN456');
END;
ORA-00001: unique constraint (ME.PATIENT_PK) violated
ORA-06512: at "ME.PATIENT_PKG", line 21

Add the same exception handler to the JSON procedure.

PROCEDURE add_patient ( p_name VARCHAR2,
                         p_os NUMBER,
                         p_mrn VARCHAR2 ) IS
  v VARCHAR2(100);
BEGIN
  v := JSON_OBJECT( KEY 'OS' VALUE p_os,
                    KEY 'MRN' VALUE p_mrn );
  v := JSON_ARRAY(v FORMAT JSON);
  v := JSON_OBJECT( KEY 'pname' VALUE p_name,
                    KEY 'mrns' VALUE v FORMAT JSON);
  INSERT INTO json_patient(patient_data) VALUES(v);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- raise uniqueness errors out to the calling program
    RAISE;
  WHEN OTHERS THEN NULL;
END;

And an example call showing the exception raised.

BEGIN
  jpatient_pkg.add_patient('Patient 1',1,'MRN8888');
END;
ORA-00001: unique constraint (ME.J) violated ORA-06512:
at "ME.JPATIENT_PKG", line 14

This works fine for relational data and for JSON data that is not in an array. The next post will discuss how array values can be handled.

Thanks for reading!

Leave a Comment