Setting a Primary or Candidate Index

You create primary and candidate indexes with the CREATE TABLE or ALTER TABLE commands. You can use both candidate and primary indexes in defining the "one" side of a one-to-many or a one-to-one persistent relationship.

To create a primary or candidate index

• In the Table Designer, select the Index tab and create an index, selecting Primary or Candidate as the index type.

  -or-

• Use the ALTER TABLE – SQL Command.

For example, either of the following commands make cust_id the primary key of the customer table:

ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id
ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY

Primary and candidate indexes are stored in the structural .cdx file for a given table. The database stores the names of the indexes in the .cdx file and whether or not the indexes are Primary or Candidate. It is not possible to store these types of indexes in other .cdx files, nor is it possible to use .idx files for these types of indexes. The principal reason is that the index file that contains these indexes should always be open whenever their associated table is opened.

Primary keys are part of a table within a database. If you free a table from a database, the primary key is removed.

If you use a user-defined function in an index expression associated with a database, Visual FoxPro handles the expression in the same way it handles rule and trigger expressions that contain UDFs.

Controlling Entry of Duplicate Values in a Field

You might want to control entry of duplicate values in a field. For example, each customer should have a unique value in the Cust_ID field of the Customer table.

You can use the field as a key field that uniquely identifies a record. If your table is part of a database, you can use either a primary or candidate index. If your table is a free table or already has a primary index, you must use a candidate index.

To create an index that enforces unique values in a field

1. In the Project Manager, select the table you want to add an index to and choose Modify.
2. In the Indexes tab, create an index.
3. In the Type column, select Primary or Candidate.
4. Choose OK.

See Also

Index Information Display | Permitting Duplicate Values | Working with Records | Creating Multiple Indexes | Creating One Index | Ordering by Multiple Fields | Project Manager Window | Index Creation for Tables