The LA Fox Developer Newsletter

December 1998

Integers (Con’t from page 10)

underlying meaning to the record it identifies. Integers are a perfect choice for surrogate keys because you easily achieve all the attributes of unique and primary keys.

Natural/intelligent/Business Key - A unique key, either simple or compound, that has meaning other than as a key. Such as a social security number.

to uniquely identify one-and-only-one record in that table. A unique key has these attributes.

It uniquely identifies the record that contains it.

it’s job.

It should not be Compound/Concatenated.

used to uniquely identify one record. The primary key is value to which records in related tables point to establish the relationship between the tables. In essence, primary key is a unique key used as the relate field. A table may have many unique key fields but only one Primary Key field.

same string as a primary key value in the child’s parent table. This string is used as a reference to the specific record in the parent table. This string may contain additional information. But, the portion of the string used to reference the parent record matches the parent primary key value exactly.

primary key to which records in other tables [ secondary] can be related. Records in a parent table can relate to none, one or many records in secondary tables.

Child Table - A table containing a record or records containing a foreign key relating to a single record in the parent table. This relationship may be one-to-one or One-to- many.

Database - An entity consisting of a collection of tables. Tables in this collection tables may be related via primary key foreign key references.

Establishing Unique Keys

The FoxPro database model uses indexes to establish the uniqueness required for Primary and Unique Keys. There are four types of keys on which to index:

Primary

Candidate

Regular

Unique

Primary index (PrimaryKey)

The Primary indexes type is only available for tables contained in a .DBC. This index type ensures that only unique values are entered into a field and determine the order the records are processed. You can create one primary index for each table included in a database. If the table already has a primary index, add a candidate index.

Candidate index (Unique Key)

Candidate indexes also enforce unique values and determine the order the records are processed, like a primary index. However you may have multiple candidate indexes per table in both database and free tables.

Regular index (or Normal)

Regular indexes determine the order records are processed but they allow duplicate values to be entered into a field. You can add more than one regular index to a table.

Unique

Selects and orders a subset of records based on the first occurrence of a value in the field you specify. If you want to select records this way, you might want to create a query or view instead. For compatibility with previous versions. Your best bet not to us. the Unique index type. Use the Primary or Candidate types.

Recommendations

definition simple. They exist solely to establish relationships and should not contain business information.

keys . - they are by definintion simple. They exist solely to establish relationships and do not contain business informa tion.

Integers are the best data type to use for keys . - They provide outstanding performance and allow you to easily create surrogate keys.

References:

FoxPro Advisor, May 1997, Real Programmers Use Integers,

page 26, Malcolm Rubel

FoxPro Advisor, June 1997, Defining Terms, page 26, Malcolm

Rubel

FoxPro Advisor, October 1997, Primary Keys: Are We Finished

Yet?, page 28, Malcolm Rubel

3.

Page 11