Choosing Data Types
As you create each table field you also choose a data type for the data the field is to store. When you choose a field's data type, you're deciding:
What kind of values to allow in the field. For example, you can't store text in a Numeric field.
How much storage space Visual FoxPro is to set aside for the values stored in that field. For example, any value with the Currency data type uses 8 bytes of storage.
What types of operations can be performed on the values in that field. For example, Visual FoxPro can find the sum of Numeric or Currency values but not of Character or General values.
Whether Visual FoxPro can index or sort values in the field. You can't sort or create an index for Memo or General fields.
Tip For phone numbers, part numbers, and other numbers you don't intend to use for mathematical calculations, you should select the Character data type, not the Numeric data type.
Choosing a Data Type
Each field in your table holds a particular type of data. You can set the data type of a field to any of the types in the following table.
| Data Type | Description | Example |
|---|---|---|
| Character | Alphanumeric text | A customer's address |
| Currency | Monetary units | Purchase price |
| Numeric | Integer or decimal numbers | Quantity of items ordered |
| Float | Same as Numeric | |
| Date | Month, day, and year | Date an order was placed |
| DateTime | Month, day, year, hours, minutes, and seconds | Date and time an employee arrived at work |
| Double | Double-precision number | Data from experiments that require a high degree of precision |
| Integer | Non-decimal numeric values (whole numbers) | Line number in an order |
| Logical | True or False | Whether or not an order has been filled |
| Memo | Alphanumeric text of an indeterminate length | Notes about phone calls in a phone log |
| General | OLE | Microsoft Excel worksheet |
| Character (Binary) | Same as Character but values aren't translated when the code page changes | User passwords stored in a table and used in different countries/regions |
| Memo (Binary) | Same as Memo but values aren't translated when the code page changes | A logon script used in different countries/regions |
To choose a data type for a field
In the Table Designer, choose a data type from the Type list.
-or-
Use the CREATE TABLE command.
For example, to create and open the table products with three fields, prod_id, prod_name, and unit_price, you could issue the following command:
CREATE TABLE products (prod_id C(6), prod_name C(40), unit_price Y)
In the previous example, the 'Y' after the unit_price field name specifies a Currency data type.
Adding a Regular Index Quickly
As you add a field, you can quickly define a regular index on the field by specifying ascending or descending in the Index column of the Table Designer. The index you create is automatically added to the Index tab and uses the field as the expression. To modify the index, you can switch to the Index tab to change the index name, type, or to add a filter.
Using Null Values
As you build a new table, you can specify whether one or more table fields will accept null values. When you use a null value, you are documenting the fact that information that would normally be stored in a field or record is not currently available. For example, an employee's health benefits or tax status may be undetermined at the time a record is populated. Rather than storing a zero or a blank, which could be interpreted to have meaning, you could store a null value in the field until the information becomes available.
To control entering null values per field
In the Table Designer, select or clear the Null column for the field.
When the Null column is selected, you can enter null values in the field.
-or-
Use the NULL and NOT NULL clauses of the CREATE TABLE command.
For example, the following command creates and opens a table that does not permit null values for the cust_id and company fields but does permit null values in the contact field:
CREATE TABLE customer (cust_id C(6) NOT NULL, ;
company C(40) NOT NULL, contact C(30) NULL)
You can also control whether null values are permitted in table fields by using the SET NULL ON command.
To permit null values in all table fields
In the Table Designer, select the Null column for each table field.
-or-
Use the SET NULL ON command before using the CREATE TABLE command.
When you issue the SET NULL ON command, Visual FoxPro automatically checks the NULL column for each table field as you add fields in the Table Designer. If you issue the SET NULL command before issuing CREATE TABLE, you don't have to specify the NULL or NOT NULL clauses. For example, the following code creates a table that allows nulls in every table field:
SET NULL ON
CREATE TABLE test (field1 C(6), field2 C(40), field3 Y)
The presence of null values affects the behavior of tables and indexes. For example, if you use APPEND FROM or INSERT INTO to copy records from a table containing null values to a table that does not permit null values, then appended fields that contained null values would be treated as blank, empty, or zero in the current table.
Adding Comments to Fields
After you create a table in an open database, you can add a description of each table field to make your tables easier to understand and update. Visual FoxPro displays a field's comment text in the Project Manager when you select the field in the list of fields for the table.
To add a comment to a field in a database table
In the Table Designer, select the field.
In the Field Comment box, enter the text for your comment.
Choose OK.
-or-
- Use the DBSETPROP( ) function.
For example, you might want to clarify what is stored in the unit_price field in your orditems table by entering "Current retail price per unit" as comment text for the field:
?DBSETPROP('orditems.price', 'field', 'comment', ;
'Current retail price per unit')
See Also
Naming Fields | Creating Default Field Values | Working with Tables | Table Designer | CREATE TABLE | Data and Field Types | Handling Null Values | Creating Databases | Restricting Access to Fields