Creating Persistent Relationships

You can create persistent relationships between tables in a database. Persistent relationships are relationships between database tables that are stored in the database file and are:

• Automatically used as default join conditions in the Query and View Designers.
• Displayed in the Database Designer as lines relating table indexes.
• Displayed in the Data Environment Designer as default relationships for forms and reports.
• Used to store referential integrity information.

Unlike temporary relationships created with the SET RELATION command, persistent relationships do not need to be re-created each time you use tables. However, because persistent relationships do not control the relationship between record pointers in tables, you will use both temporary SET RELATION relationships and persistent relationships when developing Visual FoxPro applications.

In Visual FoxPro, you use indexes to establish persistent relationships between tables in a database. You create a persistent relationship between indexes rather than between fields, which enables you to relate tables based on a simple or a complex index expression.

To prepare for relationships

1. Determine which table has the primary records and which table has the related records.

2. To the table with the primary record, add an integer field, then add a primary index on the new field.

3. To the table with the related records, add a field that matches the primary key in the other table, then add a regular index on that new field.

   Note   Use the same expression for both indexes. For example, if you use a function in the expression on the primary key field, you need to use the same function in the expression on the foreign key field.

To create a persistent relationship between tables

• In the Database Designer, choose the index name you want to relate, then drag it to the index name of the related table.

  -or-

• Use the FOREIGN KEY clause with the CREATE TABLE or ALTER TABLE commands.

For example, the following command adds a one-to-many persistent relationship between the customer and orders table, based on the primary cust_id key in the customer table, and a new foreign key, cust_id, in the orders table:

ALTER TABLE orders;
   ADD FOREIGN KEY cust_id TAG ;
      cust_id REFERENCES customer

If you were to then examine the database schema in the Database Designer, you would see a line joining orders and customer, representing the new persistent relationship.

Indexes provide the basis for persistent relationships

The type of index tag or key determines the type of persistent relationship you can create. You must use a primary or candidate index tag or key for the "one" side of a one-to-many relationship; for the "many" side, you must use a regular index tag or key.

You can also edit the relationship.

To edit a relationship between tables

• Double-click the relationship line between the tables, and then select the appropriate settings in the Edit Relationship dialog box.

To delete a persistent relationship between tables

1. In the Database Designer, click the relationship line between the two tables.

   The width of the relationship line increases to indicate that you have selected the relationship.

2. Press the DELETE key

   -or-

   Use the DROP FOREIGN KEY clause with the ALTER TABLE command.

For example, the following command deletes a persistent relationship between the customer and orders table, based on the primary cust_id key in the customer table, and a foreign key, cust_id, in the orders table:

ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE

See Also

Updating Table and Database Links | Building Referential Integrity | Creating Databases | Creating a Database | Database Designer | CREATE TABLE | ALTER TABLE | Working with a Database | Working with Tables