esProc’s Foreign Key and Relationships Between Tables

In relational databases, foreign key is often used to handle relationshipsbetween tables. In esProc, this kind of corresponding relationships can also be represented by foreign key fields. 

1.Computed columns and foreign key fields 

T.derive() function can be used in esProc to add computed columns to a table sequence, for example: 

A1 gets employee information. After computed columns FULLNAME and AGE are added into A1's table sequence, A2 returns the new table sequence. Compute employees’ full names and ages.

The following is the table sequence in A1:

After the computed columns are added, table sequence of A2 becomes like this: 

Knowing about how to add computed columns to a table sequence, we'll look at the relation between computed columns and relationships between tables.
In databases, there often exist relationships between tables; in esProc, by directly using records' references as data of table sequences, this kind of relationship is displayed and data search and presentation become simple and clear.

If T.derive() function is used to add a computed column to a table sequence, making the data type of this column a reference of the records or record sequences of another table, then foreign key fields can be created. Thus the relationships between tables can be realized. For example:

A1 and A2 retrieve respectively data of database tables: STATES and CITIES: 

CITIES is correlated with STATES through STATEID field. This kind of storage pattern in  databases can keep data consistency, make data easy to maintain and save storage space.

A3 adds State field as a foreign key in CITIES to store record of state in which cities located. Again, A4 adds SA field in CITIES to list abbreviations of these states, making them convenient to be looked up for the future. A5 adds Cities field in STATES as a foreign key to store records of cities of every state.

Execute the program and data of A4 are as follows:

State field contains records of states information, double-click and see more.

Data of A5 are as follows:

Cities field contains records of cities of each state, double-click to see more.

It can be seen that, through foreign keys, in fact data of both A4 and A5 has obtainedall information of the two tables: STATES and CITIES, in the original database, thus relationship between tables is realized. Note that there are many types of foreign key fields. Data of foreign key State in A4 are records while those of foreign key in A5 are record sequences, i.e. sequences of records.

2.Use of foreign key fields

During querying or presenting table sequences, the use of foreign key fields is the same as that of common fields. Data types of foreign key fields are what we should note in using them.

For example, select from CITIES the states whose names contain "la". Then directly call NAME field of foreign key State to filter data with filter expression: 

Note that here the type of foreign key field is record. Computed results of A6 are as follows: 

Or, foreign key fields can be used in sorting criterion, for example, sort the states in descending order according to the number of cities records of the current state:

Note that here the type of foreign key fields is record sequence. Sorting results of A6 are as follows:

If the records referenced by a certain foreign key field of a table sequence still contain foreign key fields, these records can be referenced again. For example, list information of cities that the states in which they are located happen to contain three cities:

Here A4 uses states information containing foreign key fields which are generated in A3, instead of the original states information while adding states information into the CITIES. In this way, results we need can be seen in A5: