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.

Unique

Key - A value in each record of a table that can be used

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 contains the minimum amount of data to perform

it’s job.

It should not be Compound/Concatenated.

Primary

Key - A value in each record of a table that can be

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.

Foreign

Key - A value in a child table that contains exactly the

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.

Parent

Table - The master or controlling table, containing the

primary key to which records in other tables [ second-

ary] 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

1.

Simple keys are better than compound keys. - They are by

definition simple. They exist solely to establish relation-

ships and should not contain business information.

2.

Surrogate keys are better than natural/intelligent/business

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