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.
 
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 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.

For more information about how null values interact with Visual FoxPro commands, see Handling Null
Values.