Databases in Visual FoxPro
Databases provide an architecture and working environment for associating, organizing, and working with tables and views. You can use databases independently or include them in an application project. When you create a database, Visual FoxPro creates and opens a database (.dbc) file exclusively. The database file, which is a table, stores all the information about the database including the names of files and items associated with it. The database file does not physically contain top-level items such as tables or fields. Instead, the database file stores file path pointers to tables associated with the database.
Databases provide additional benefits such as the following:
Associate tables with a database.
Create persistent relationships between tables.
Make it possible for you to create field and record-level validation rules without writing code.
Create rules to preserve the relationships between database tables when adding, updating, or deleting records.
Create stored procedures.
Create views from local and remote tables.
Access connections to remote data sources.
To view the structure of a sample database, open the NorthWind.dbc file located in the Visual FoxPro ...\Samples\Northwind directory. For more information, see How to: Open Databases.
Data Dictionary Features of Databases
Creating a database by associating tables as a group provides the benefits of the data dictionary features in Visual FoxPro. A data dictionary provides greater flexibility in designing and modifying the database and frees you from writing code to create field and row-level validation or to ensure the uniqueness of values in primary key fields. More specifically, the Visual FoxPro data dictionary makes it possible for you to create or specify the following items:
Primary and candidate keys.
Persistent relationships between database tables.
Long names for tables and fields.
Captions on fields that display in browse windows and grid columns as headers.
Default values in fields.
The default control class used in forms.
Input masks and display formats for fields.
Field-level rules and record-level rules.
Triggers.
Stored procedures.
Connections to remote data sources.
Local and remote views.
Comments for each field, table, and database.
Some data dictionary features, such as long field names, primary and candidate keys, default values, field-level and record-level rules, and triggers, are stored with the database (.dbc) file but can also be created by creating a table or view. For more information, see Working with Tables and How to: Create Views.
Tables in Databases
You can associate tables with a database by creating them or adding them to a database. Tables in Visual FoxPro can exist as a free table or as a database table. A free table is a table (.dbf) file that is not associated with any database. A database table is a table file that is associated with a database. Database tables can have properties that free tables do not have, such as field-level and record-level rules, triggers, and persistent relationships. For more information, see Working with Tables and How to: Associate Tables with a Database.
Links Between Tables and the Database
Adding a table to the database creates links between the table file and the database. Information for these links is stored in the database (.dbc) file and the table (.dbf) file. The link information stored in the database file about a table it contains is called a forward link. Forward link information consists of the relative path and file name for each associated table file and is stored in the database file.
The link information stored in the table file about the database that contains it is called a back link. Back link information consists of the relative path and file name for the database associated with the table and is stored in the header of the table file. For more information, see How to: Update Table and Database Links.
Persistent Relationships Between Database Tables
Creating persistent relationships between database tables in a database make it possible for you to relate tables based on a simple or complex index expression or their indexes so you can access the exact records you want.
Indexes provide the basis for persistent relationships
.gif)
Unlike temporary relationships created with the SET RELATION command, you do not need to recreate persistent relationships each time you use the tables. However, persistent relationships do not control the relationship between record pointers in tables, so you need to set temporary relationships using the SET RELATION command relationships as well as persistent relationships when developing Visual FoxPro applications. For more information, see SET RELATION Command.
Persistent relationships are stored in the database (.dbc) file. The following also apply to persistent relationships:
They are used automatically as the default join conditions in the Query and View designers. For more information, see Query and View Designers.
They appear in the Database Designer as lines joining table indexes. For more information, see Database Designer.
They are used to store referential integrity information.
They are used as the default relationships for forms and reports in a data environment and appear in the Data Environment Designer. For more information, see Data Environment Designer.
For more information, see How to: Create Persistent Relationships Between Tables.
Referential Integrity Between Database Tables
You can create a set of rules to preserve relationship definitions between database tables when adding, updating, or deleting records. The process of creating these rules is called building referential integrity. When you create rules to enforce referential integrity, Visual FoxPro prevents the following actions:
Adding records to a related table when no associated record exists in the primary table.
Changing values in a primary table that result in orphan records in a related table.
Deleting records from a primary table when there are matching related records.
You can write triggers and stored procedure code to enforce referential integrity. A trigger is an expression that is bound to a table and is invoked when any of the table's records are modified using a one of the specified data manipulating commands. However, you can use the Visual FoxPro Referential Integrity (RI) Builder to build referential integrity rules.
The RI Builder makes it easier for you to determine the types of rules you want to enforce, the tables to enforce the rules with, and the system events that cause Visual FoxPro to check referential integrity rules. The RI Builder can also handle multiple levels of cascading for cascaded deletes and updates. When you use the RI Builder, Visual FoxPro generates the code for enforcing relational integrity rules and saves it as triggers that reference stored procedures. For more information, see How to: Build Referential Integrity Between Tables.
Stored Procedures in Databases
A stored procedure is Visual FoxPro procedure code that operates specifically on the data in a database and is stored in the database (.dbc) file. Stored procedures can improve performance because they are loaded into memory when a database opens. Stored procedures also make your application more portable because you do not have to manage code files separate from the database file.
For example, you can use stored procedures for creating user-defined functions that you reference in validation rules at the field and record level. When you save a user-defined function as a stored procedure in a database, the function code is saved in the database file and moves with the database automatically if you relocate the database. For more information, see How to: Create and Manage Stored Procedures.
Multiple Databases for Multiple User Environments
To meet organizational needs in a multiple user environment, you might need more than one database on your system. Multiple databases offer the following advantages:
Controlling user access to a subset of tables in the overall system.
Organizing data to meet diverse informational needs efficiently for various user groups.
Making it possible to use a subset of tables exclusively for creating local and remote views at run time.
For example, suppose you have a database with sales information that is used primarily by a sales force working with customers and another database that maintains inventory information that is used primarily by buyers working with suppliers. Sometimes, the informational needs for these groups overlap. Multiple databases can be opened and accessed at the same time; yet they contain different information.
Multiple databases can add flexibility to your system
.gif)