How to: Create Persistent Relationships Between Tables
You can create persistent relationships between database tables based on a simple or complex expression or their indexes. These relationships are stored in the database (.dbc) file. The type of index tag or key determines the type of persistent relationship you can create. For example, in a one-to-many relationship between two tables, you must use a primary or candidate index tag or key in a table for the "one" side in the relationship and a regular index tag or key in the other table for the "many" side in the relationship.
Before you can create a persistent relationship, you need to perform the following steps:
Determine which table contains the primary records and which table contains the related records.
In the table with the primary records, add an integer field, and then add a primary index on the new field.
In the table with the related records, add a field that matches the primary index key in the primary record table, and 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.
For more information, see Identifying Relationships and Working with Table Indexes.
To create a persistent relationship between tables
Open the database in the Database Designer.
In the Database Designer, drag the index name from the table you want to relate to the index name in the related table.
A line representing the persistent relationship between the two tables appears in the Database Designer.
For more information, see Database Designer (Visual FoxPro).
To create a persistent relationship between tables programmatically
- Use the CREATE TABLE or ALTER TABLE commands with the FOREIGN KEY clause.
For more information, see CREATE TABLE - SQL Command and ALTER TABLE - SQL Command.
For example, the following code uses the SQL ALTER TABLE command and the tables Customer and Orders to create a one-to-many persistent relationship, where one customer has many orders, based on the primary index key, Cust_ID, 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
See Also
Tasks
How to: Manage Persistent Relationships Between Tables