Foreign key fields

In relational databases, hierarchies can be logically built by creating foreign key relationships between tables. In IMS, the hierarchies are explicit and are part of the database definition itself. The IMS Universal JDBC driver introduces the concept of foreign keys to capture these explicit hierarchies in a relational sense, which makes the SQL syntax for IMS equivalent to standard SQL.

When accessing IMS databases with the IMS Universal JDBC driver, every table that is not the root table in a hierarchic path will virtually contain the unique keys of all of its parent segments up to the root of the database. These keys are called foreign key fields.

For segments with secondary indexes, the secondary index is also the primary key of the segment. Foreign keys that correspond with the segment are derived from the name of the secondary index.

Restriction: Secondary indexes cannot be referenced in SELECT statements. They can only be referenced in WHERE clauses.

The purpose of the foreign key fields is to maintain referential integrity, similar to foreign keys in relational databases. This allows SQL SELECT, INSERT, UPDATE, and DELETE queries to be written against specific tables and columns located in a hierarchic path.

Remember: Foreign keys are maintained internally by the IMS Universal JDBC driver; the keys are not physically stored in the IMS database.

Hospital database example without a secondary index

For example, in the Hospital database, the HOSPITAL, WARD, and PATIENT tables are on the same hierarchic path. The JDBC application would view the tables as containing the following columns.

HOSPITAL table

Columns are:
  • HOSPNAME
  • HOSPCODE (primary key)

WARD table

Columns are:
  • WARDNO (primary key)
  • WARDNAME
  • PATCOUNT
  • NURCOUNT
  • DOCCOUNT
  • HOSPITAL_HOSPCODE (foreign key field referencing the HOSPCODE column in the HOSPITAL table)

PATIENT table

Columns are:

  • PATNUM (primary key)
  • PATNAME
  • WARD_WARDNO (foreign key field referencing the WARDNO column in the WARD table)
  • HOSPITAL_HOSPCODE (foreign key field referencing the HOSPCODE column in the HOSPITAL table)
The following queries show how SQL SELECT statements can use foreign keys, based on the previous database example. The following statement retrieves all columns from a PATIENT table derived from a child segment under HOSPITAL and WARD on a hierarchic path:
SELECT * FROM PCB01.PATIENT 
WHERE HOSPITAL_HOSPCODE = 'H5140070000H'
   AND WARD_WARDNO = '0023'
The following example shows an INSERT statement using foreign keys:
INSERT INTO PCB01.PATIENT (PATNUM, PATNAME, 
WARD_WARDNO, HOSPITAL_HOSPCODE) 
VALUES ('00345', 'John Doe', '0023', 'H514007000H')
The following statements retrieve the hospital code and all ward names from a WARD table. These statements are all equivalent:
SELECT HOSPITAL.HOSPCODE, WARD.WARDNAME
FROM PCB01.HOSPITAL, PCB01.WARD
SELECT HOSPITAL_HOSPCODE, WARD.WARDNAME
FROM PCB01.WARD
SELECT WARD.HOSPITAL_HOSPCODE, WARD.WARDNAME
FROM PCB01.WARD
SELECT HOSPITAL_HOSPCODE, WARDNAME
FROM PCB01.WARD
The following statement will fail because the column HOSPITAL_HOSPCODE is not in the table HOSPITAL.:
SELECT HOSPITAL_HOSPCODE FROM PCB01.HOSPITAL

Hospital database example with a secondary index

The following example shows how the previous example would change if the HOSPITAL table had a secondary index.

HOSPITAL table

Columns are:
  • HOSPNAME (secondary index and primary key)
  • HOSPCODE

WARD table

Columns are:
  • WARDNO (primary key)
  • WARDNAME
  • PATCOUNT
  • NURCOUNT
  • DOCCOUNT
  • HOSPITAL_HOSPNAME (foreign key field referencing the HOSPNAME secondary index in the HOSPITAL table)

PATIENT table

Columns are:

  • PATNUM (primary key)
  • PATNAME
  • WARD_WARDNO (foreign key field referencing the WARDNO column in the WARD table)
  • HOSPITAL_HOSPNAME (foreign key field referencing the HOSPNAME secondary index in the HOSPITAL table)