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.
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.
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
- HOSPNAME
- HOSPCODE (primary key)
WARD table
- 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)
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
- HOSPNAME (secondary index and primary key)
- HOSPCODE
WARD table
- 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)