Field Creation
You can define fields by specifying a field name, a data type, and a field width. When you create a table, you need to define the fields that the table contains. Visual FoxPro tables can contain up to 255 fields.
Note
If one or more fields can store null values, the maximum number of fields is reduced from 255 to 254.
The following list describes the general attributes you specify when creating a field:
Name of the field.
Type of data that the field contains.
Width of the field.
Number of decimal places if the field contains numeric or float values.
An index on the field.
Whether the field accepts null values.
You can also perform the following tasks when working with fields:
Specify default values for the field.
Add comments for the field.
Specify input masks for entering and storing data in the field.
Set automatically incrementing values in fields.
The following sections describes considerations when creating fields:
Considerations for Choosing Field Names
Considerations for Choosing Data Types
Considerations for Default Values in Fields
Considerations for Choosing Field Names
For database fields, Visual FoxPro stores the first 10 characters of the field name in the table (.dbf) file and the long name for the field in the database (.dbc) file. The rules for creating long field names are the same as those for creating any Visual FoxPro identifier except that database field names can contain up to 128 characters. For more information, see Creating Visual FoxPro Names.
However, as long as a table is associated with a database, you must use the long field names to refer to table fields, not the 10-character field names. If you remove a table from a database, the long names for the fields are lost, and you must use the 10-character field names stored in the table (.dbf) file as the field names.
If the first 10 characters in the long name are not unique in the table, Visual FoxPro generates a field name consisting of the first n characters in the long name appended by a sequential number for a total of 10 characters. The following table lists examples of generated names.
Long name |
Short name |
|---|---|
customer_contact_name |
customer_c |
customer_contact_address |
customer_2 |
customer_contact_city |
customer_3 |
... |
... |
customer_contact_fax |
customer11 |
In indexes, long field names can consist of characters, but not numbers. However, if you create an index using long field names and then remove the referenced table from the database, the index no longer functions correctly. In this case, you can shorten the field names in the index and then rebuild the index, or you can delete the index and recreate it using short field names. For more information, see Working with Table Indexes.
For more information about choosing field names, see How to: Name Fields.
Considerations for Choosing Data Types
When you choose the data type for a field, remember the following considerations:
The types of values the field can store.
For example, you cannot store text in a numeric field.
The amount of storage the field needs to store values.
For example, any value with Currency type uses 8 bytes of storage.
The types of operations you want to perform on values in the field.
For example, Visual FoxPro can calculate the sum of values with Numeric or Currency type but not the sum of values with Character or General type.
Tip
For numbers that you do not intend to use for mathematical calculations, for example, phone numbers, use the Character, not Numeric, type.
The capability to index or sort values in the field.
For example, you cannot sort or create an index for fields with Memo or General type.
For more information about Visual FoxPro data types, see Visual FoxPro Data and Field Types. For more information about choosing data types for fields, see How to: Choose Data Types.
Considerations for Default Values in Fields
Specifying default values for fields help application users enter data more quickly by making it possible to skip fields unless they want to change the default value.
Tip
If a business rule for your application requires that a field contain an entry, providing a default value helps to comply with that rule using a field-level or record-level rule.
For example, suppose a company has primarily domestic customers. On the form used to enter data for new customers, you might want to display the country for those customers as the default value in the country or region field. If other values are required for the country or region, the user can replace the default value with the appropriate value.
You can specify default values that are scalar values, such as a number, or expressions that evaluate to a scalar quantity. You can also specify any valid FoxPro expression that returns a value consistent with the data type for the field.
Note
When specifying the default value, Visual FoxPro evaluates the default value or expression for the correct data type when the closing the table structure or using the SQL CREATE TABLE or ALTER TABLE commands. If the data type of the expression does not match the data type of the field, Visual FoxPro generates an error. Expressions that are user-defined functions (UDF) or contain UDFs are not evaluated; and therefore, do not generate errors.
Visual FoxPro evaluates the default value or expression when using the APPEND, APPEND BLANK, or SQL INSERT commands to assign values to fields. When you assign values to fields using the APPEND FROM or SQL INSERT commands, Visual FoxPro applies default values to any fields that are not explicitly assigned. However, these two commands do not replace any existing values with default values.
You can also use default values to populate fields that do not accept null values. When you add a new record, Visual FoxPro applies the default values then checks each field in the order they are defined for missing data. This makes sure that fields that do not accept null values can be populated with default values before applying the constraint that the fields cannot be null.
If fields in your tables accept null values, you can use the null value (.NULL.) as the default value. Regardless of whether SET NULL is on or off, if you use null (.NULL.) as the default value, Visual FoxPro inserts null (.NULL.) into fields for all commands except APPEND BLANK. For more information, see SET NULL Command and How to: Permit Null Values in Fields.
If you do not specify a default value, a blank value appropriate for the data type of the field is inserted unless the SET NULL command is set to ON. This preserves backward compatibility.
Note
When you remove or delete a table from a database, all the default values for fields in that table are deleted from the database. However, stored procedures referenced by the removed or deleted default value remain even after the default value has been removed.
For more information about setting default values for fields, see How to: Create Default Field Values.