2021 – Add Entry To Child Table

This post explains how a child record is added for a parent record. These terms (parent and child record) are part of the Relational Database Management System (RDBMS) nomenclature. The fully expanded description would be:

Create a new record in a child table that is related to the parent table via a Foreign Key (FK) to a Primary Key (PK) in the parent table.

The demonstration code below creates a new record in the child table (PATIENT_MRN) for an associated parent table (PATIENT) record. The code satsiies the following new requirement for our patient tracking system:

Add a new Ordering System (OS) / Medical Record Number MRN to an existing patient. Ensure the OS/MRN are unique across all patient records.

Let’s dive right into the procedure…

CREATE OR REPLACE PROCEDURE add_mrn ( p_name VARCHAR2,
                                      p_os NUMBER,
                                      p_mrn VARCHAR2 ) AS
BEGIN
  -- this simple looking SQL does all the work
  -- !! its known as the INSERT INTO SELECT STAR FROM (IISSF) statement !!
  --    1) Ensure the patient exists by querying its patient_id
  --       from the patient table based on the passed in name
  --    2) Includes the OS and MRN passed in the new record
  --    3) Does not need to check for uniqueness because the
  --       unique key in the database will take care of that
  --    4) Does not need to check datatypes because they are
  --       dictates by the parameter datatypes and the datatypes
  --       of columns in the database table
  INSERT INTO patient_mrn(patient_id,
                          ordering_system,
                          patient_mrn)
  SELECT patient_id,
         p_os,
         p_mrn
    FROM patient
   WHERE patient_name = p_name;
END;

Before we test it lets see the current test data. One patient with two MRN’s and one patient with one MRN.

SELECT patient_name,
       ordering_system,
       patient_mrn
  FROM patient p,
       patient_mrn mrn
 WHERE p.patient_id = mrn.patient_id;

PATIENT_NAME ORDERING_SYSTEM MRN
------------ --------------- ------
Patient 1                  1 MRN123
Patient 1                  2 MRN456
Patient 2                  1 MRN99

Now test the procedure.

-- Add a new MRN for a patient that exists
BEGIN
  add_mrn('Patient 2',2,'MRN4321');
END;

Query the data again and here is what we see – both patients have two MRN’s.

PATIENT_NAME ORDERING_SYSTEM MRN
------------ --------------- ------ 
Patient 1                  1 MRN123
Patient 1                  2 MRN456
Patient 2                  1 MRN99
Patient 2                  2 MRN4321

Next we’ll attempt to add the same OS/MRN combination again. It should raise a unique constraint error.

-- Add an MRN that already exists
BEGIN
  add_mrn('Patient 2',2,'MRN4321');
END;
-- Success! Raises ORA-00001: unique constraint (ME.OS_MRN_UNIQUE)

Another requirement for this procedure is that it ignore all errors except for the following:

  • Unique constraint violations
  • Patients that do not exist

That is done by adding the exception handlers shown below…or is it…

CREATE OR REPLACE PROCEDURE add_mrn ( p_name VARCHAR2,
                                      p_os NUMBER,
                                      p_mrn VARCHAR2 ) AS
BEGIN
  INSERT INTO patient_mrn(patient_id,
                          ordering_system,
                          patient_mrn)
  SELECT patient_id,
         p_os,
         p_mrn
    FROM patient
   WHERE patient_name = p_name;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN NULL;  -- raise error on duplicates
  WHEN NO_DATA_FOUND THEN RAISE;    -- raise error when patient not found?
  WHEN OTHERS THEN NULL;           -- ignore all others
END;

And we verify the silent failure when adding a duplicate OS and MRN.

-- silent failure!
BEGIN
  add_mrn('Patient 2',2,'MRN4321');
END;

But what if the patient name is not found? Will the IISF statement raise an error?

BEGIN
  add_mrn('Patient Not There',2,'MRN4321');
END;

No exception was raised because the IISSF statement does not return any records (or data) to the procedure whether it inserts a record or not. Thus the NO_DATA_FOUND exception never fires. We need to force it to fire if no row is created using the SQL%ROWCOUNT session variable as shown here.

CREATE OR REPLACE PROCEDURE add_mrn ( p_name VARCHAR2,
                                       p_os NUMBER,
                                       p_mrn VARCHAR2 ) AS
BEGIN
  INSERT INTO patient_mrn(patient_id,
                          ordering_system,
                          patient_mrn)
  SELECT patient_id,
         p_os,
         p_mrn
    FROM patient
   WHERE patient_name = p_name;
  IF SQL%ROWCOUNT = 0 THEN
    RAISE NO_DATA_FOUND; -- force NDF exception
  END IF;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN NULL;  -- raise error on duplicates
  WHEN NO_DATA_FOUND THEN RAISE;    -- raise error when patient not found!
  WHEN OTHERS THEN NULL;            -- ignore all others
END;

Now it gets raised.

BEGIN
  add_mrn('Patient Not There',2,'MRN4321');
END;
Raises ORA-01403: no data found ORA-06512: at "ME.ADD_MRN", line 18

The main take-away from this post is:

RDBMS structures take care of data relationships and datatypes which allows developers to focus on business requirements.

Thanks for reading!

Leave a Comment