Comment on this topic

Documentation Comments

Use this form to comment on this topic. You can also provide any general observations about the Online Documentation, or request that additional information be added in a future release.

Reality V15.2 Online Documentation (MoTW) Revision 3

Choosing Primary Keys (SQL) (m691506+primarykeys.htm)

To

Reality

Version

Topic

Submitted by

Company

Location

Email address

Comment

 

 

Choosing Primary Keys

Each item in the PERSONNEL file has a Person ID that is unique to that person. See Example of a PERSONNEL Item. This may be used within a company to uniquely identify personnel, or it may be assigned by a computer. Either way it uniquely identifies a person's record within the PERSONNEL file. The Person ID in this example is called a 'primary key'.

The Person ID in the QUALIFICATIONS table is called a 'foreign key' A foreign key is a column or combination of columns in one table, whose values are required to match those of the primary key in a second table. The primary key in the QUALIFICATIONS table is formed by a combination of the Person ID and the Qualification No. (multivalue number).

Note: Almost without exception, every table should have a primary key. A table that has no primary key cannot be updated.

Where an SQL table is based on a Reality file the column or columns that correspond to the Reality item-id are generally a good choice.

Where multi- or sub-valued data is being exploded into multiple rows another column should be added to the Primary Key to uniquely identify the multi- or subvalued set. If there is no suitable column in the data itself, an artificial column should be created such as the QUAL_NO column discussed earlier. See Example 3.

RealityV15.2Revision 3Comment on this topic