After you’ve designed your database, you can build the database through the interface or with the language. You might want to add existing tables to your database, and then modify them to take advantage of the data dictionary features in Visual FoxPro. If you are working within a project in the Project Manager, you can add the tables as you create them.
This chapter discusses:
•
Creating a Database•
Viewing and Modifying Database Architecture•
Managing a Database•
Referencing Multiple Databases•
Handling Database ErrorsCreating a Database
When you create a database, you gather tables together into one collection and gain the benefit of data dictionary features.
A data dictionary gives you greater flexibility in designing and modifying the database, and frees you from having to write code to create field-level and row-level validation or to ensure the uniqueness of values within primary key fields. The Visual FoxPro data dictionary enables you to create or specify:
•
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 on 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.Collecting Tables into a Database
To collect tables into a database, you need to create a database container to hold all of the objects such as views, connections, and stored procedures associated with the tables that make up your database.
To create a new database
•
In the Project Manager, select the Data tab, then select Databases from the list and choose New.–
Or
–
•
Use the CREATE DATABASE command.For example, the following code creates and exclusively opens a new database called
sample:
CREATE DATABASE sample
Commands and functions that manipulate databases and database objects
ADATABASES(
)
CREATE VIEW
MODIFY CONNECTION
ADBOBJECTS(
)
DBC(
)
MODIFY DATABASE
ALTER TABLE
DBSETPROP(
)
MODIFY STRUCTURE
APPEND PROCEDURES
DELETE CONNECTION
MODIFY VIEW
CLOSE DATABASE
DELETE DATABASE
OPEN DATABASE
COPY PROCEDURES
DELETE VIEW
PACK DATABASE
CREATE CONNECTION
DISPLAY DATABASE
RENAME TABLE
CREATE DATABASE
DROP TABLE
REMOVE TABLE
CREATE SQL VIEW
INDBC(
)
SET DATABASE
CREATE TABLE
LIST DATABASE
VALIDATE DATABASE
Adding Tables to a Database
Each Visual FoxPro table can exist in one of two states: either as a free table, which is a .DBF file that is not associated with any database, or as a database table, which is a .DBF file that is associated with a
database. Tables associated with a database can have properties that tables outside a database do not have, such as field-level and record-level rules, triggers, and persistent relationships.
You associate tables with a database by creating them within an open database, or by adding existing tables to a database. For information about creating new tables, see Chapter 7, Working with Tables.
To add a free table to a database
•
In the Project Manager, select Tables from the All tab or the Data tab, then choose Add.–
Or
–
•
Use the ADD TABLE command.For example, the following code opens the
testdata database and adds the orditems table:
OPEN DATABASE testdata
ADD TABLE orditems
You must explicitly add an existing free table to a database to make it a part of a database. Modifying the structure of a free table does not cause Visual FoxPro to add the free table to a database, even if a database is open when you issue the MODIFY STRUCTURE command.
Using Free Tables
You can associate a given table with only one database. However, you can use the data in an existing .DBF file without incorporating it into your database.
To access a table in another database
•
Create a view in your database that references the table.–
Or
–
•
Access the table with the USE command and the “!” symbol.Use the “!” symbol to refer to a table in a database other than the current database. For example, if you want to browse the
orditems table in the testdata database, you can type:
USE testdata!orditems
BROWSE
In the previous example, the
testdata database is opened automatically for you when you issue the USE
command, but Visual FoxPro does not set testdata as the current database. A database opened
automatically as in the previous example is automatically closed when the table is closed, unless you open
the database explicitly before closing the table.
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.•
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 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 TABLEcommands.
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 relationshipsThe 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.
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
DELETEkey.
–
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
Building Referential Integrity
Establishing referential integrity involves building a set of rules to preserve the defined relationships between tables when you enter or delete records.
If you enforce referential integrity, Visual FoxPro prevents you from:
•
Adding records to a related table when there is no associated record in the primary table.•
Changing values in a primary table that would result in orphan records in a related table.•
Deleting records from a primary table when there are matching related records.You can choose to write your own triggers and stored procedure code to enforce referential integrity. However, the Visual FoxPro Referential Integrity (RI) Builder enables you to determine the types of rules you want to enforce, the tables on which you want the rules enforced and the system events that will cause Visual FoxPro to check referential integrity rules.
The RI Builder handles multiple levels of cascading for cascade deletes and updates and is recommended as a tool for building referential integrity.
To open the RI Builder
1
Open the Database Designer.
2
From the Database menu, choose Referential Integrity.
When you use the RI Builder to build rules for your database, Visual FoxPro generates code to enforce relational integrity rules, then saves it as triggers that reference stored procedures. You can view this code by opening the stored procedure text editor for your database. For information about creating triggers programmatically,
Browsing the Database File
The database file contains a record for each table, view, index, index tag, persistent relationship, and connection associated with the database, as well as for each table field or view field having extended properties. It also includes a single record that contains all the stored procedures for the database. For information about the structure of the .DBC file, search for “File Structures” in Help.
While the Database Designer provides a conceptual representation of the database schema, you sometimes might need to browse the contents of the database file itself. You can browse a closed database by issuing the USE command on the .DBC file. The following example opens a Browse window displaying the contents of the
sales database in table form.
CLOSE DATABASE sales
USE sales.dbc EXCLUSIVE
BROWSE
Caution Don’t use the BROWSE command to alter the database file unless you are knowledgeable about
the structure of the .DBC file. If you make an error while attempting to change the .DBC file you can invalidate the database and potentially lose data.
Extending Database Files
Each .DBC file contains a Memo field named User that you can use to store your own information about each record in the database. You can also extend a .DBC file to add fields to accommodate your own needs as a developer. Fields must be added to the end of the structure. You must have exclusive access to a .DBC file to modify its structure.
To add a field to a .DBC file
1
Open the .DBC file for exclusive use with the USE command.
2
Use the MODIFY STRUCTURE command.
For example, the following code opens the Table Designer so you can add a field to the structure of TESTDATA.DBC:
USE TESTDATA.DBC EXCLUSIVE
MODIFY STRUCTURE
When you add a new field to a database file, begin the field name with “U” to designate it as a user-defined field. This designation prevents your field from conflicting with any future extensions to the .DBC file.
Caution Don’t change any existing Visual FoxPro- defined fields in a .DBC file. Any changes you make to
a .DBC file could affect the integrity of your database.
Managing a Database
After creating a database, you might want to add it to a project if it isn’t already part of one. If your database is already part of a project, you can remove it from a project. Also, if you no longer need the database, you can delete it from the disk.
A database in the Project Manager
Adding a Database to a Project
When you create a database with the CREATE DATABASE command, the database is not automatically a part of a project, even if the Project Manager is open. You can add the database to a project to make it
easier to organize, view and manipulate database objects through the interface, as well as to simplify the process of building an application. You can add a database to a project only through the Project Manager.
To add a database to a project
•
In the Project Manager, select Databases and choose Add.Removing a Database from a Project
You can remove a database from a project only through the Project Manager.
To remove a database from a project
•
In the Project Manager, select the database and choose Remove, then choose Remove again.Deleting a Database
You can delete a database from disk using the Project Manager or the DELETE DATABASE command.
To delete a database
•
In the Project Manager, select the database and choose Remove, and then choose Delete.–
Or
–
•
Use the DELETE DATABASE command.For example, the following code deletes the
sample database:
DELETE DATABASE sample
Always use one of the methods above to delete a database from disk. Using the Project Manager or the DELETE DATABASE command enables Visual FoxPro to remove backlinks to the database from the tables in a database. If you use another file manipulation utility to delete a database file, such as the Windows Explorer, these backlinks are not removed.
Note The DELETE DATABASE command does not delete the tables associated with a database from the
disk; rather, the tables that were associated with the database become free tables. If you want to delete both a database and all its associated tables from disk, use the DELETETABLES clause with the DELETE DATABASE command.
Referencing Multiple Databases
You can have many Visual FoxPro databases on your system to meet organizational needs in a multi-user environment. Multiple databases offer the following advantages:
•
Controlling user access to a subset of tables in the overall system.•
Organizing the data to efficiently meet the information needs of various groups using the system.•
Allowing exclusive use of a subset of tables for creating local and remote views at run time. For example, you might have a sales database that maintains sales information used primarily by the sales force working with customers and another database that maintains inventory information used primarily by the buyers working with suppliers. At times the information needs of these groups will overlap. These databases can be opened at the same time and accessed at will but they contain completely different types of information.You can use multiple databases either by opening more than one database simultaneously or by referencing tables in a closed database. Once multiple databases are open, you can set the current database and select tables in it.
Opening More Than One Database
When a database is open, the tables and relationships between tables are controlled by the information stored in the open database. You can have more than one database open at a time. For example, you might use multiple open databases when you run multiple applications, each based on a different database. You might also want to open multiple databases to use information, such as custom controls, stored in a database that is separate from your application’s database.
•
In the Project Manager, select a database and choose Modify or Open.–
Or
–
•
Use the OPEN DATABASE command.Opening a new database does not close any databases you’ve opened previously. Other open databases remain open, and the newly opened database becomes the current database.
Setting the Current Database
When you open multiple databases, Visual FoxPro sets the most recently opened database as the current database. Any tables or other objects you create or add to the database become a part of the current database by default. Commands and functions that manipulate open databases, such as ADD TABLE and DBC(
), operate on the current database.
You can choose a different database as the current database through the interface or with the SET DATABASE command.
To set the current database
•
On the standard toolbar, select a database from the Databases box.–
Or
–
•
Use the SET DATABASE command.For example, the following code opens three databases, sets the first database as the current database, then uses the DBC(
) function to display the name of the current database:
OPEN DATABASE testdata
OPEN DATABASE tastrade
OPEN DATABASE sample
SET DATABASE TO testdata
? DBC(
)
Tip Visual FoxPro might open one or more databases automatically when you execute a query or a form
that requires the databases to be open. To be sure you are operating on the correct database, set the current database explicitly before issuing any commands that operate on the current database.
Selecting Tables in the Current Database
You can choose from a list of tables in the current database with the USE command.
To choose a table from the current database
•
Issue the USE command with a “?” symbol.The Use dialog box displays so that you can select a table to open.
For example, the following code opens the
sales database and prompts you to select a table from the list
of tables in the database.
OPEN DATABASE SALES
USE ?
If you want to select a table that is not associated with the open database, you can choose Other in the Use dialog box.
Closing a Database
You can close an open database from the Project Manager or with the CLOSE DATABASE command.
To close a database
•
From the Project Manager, select the database and choose Close.–
Or
–
For example, the following code closes the
testdata database:
SET DATABASE TO testdata
CLOSE DATABASE
Both options close the database automatically. You can also close databases and all other open objects with the ALL clause of the CLOSE command.
Issuing the CLOSE DATABASE command from the Command window does not close a database if the database was opened by:
•
The Project Manager when you expanded the outline to view the contents of a database.•
A form that is running in its own data session.In these circumstances, the database remains open until the Project Manager closes the database, or until the form using the database is closed.