How to: Permit Null Values in Fields
When you specify fields for a table, you can decide whether one or more fields can accept null (.NULL.) values. Typically, a null value indicates that information usually stored in the field or record is not currently available.
For example, an employee's health benefits or tax status might be unknown at the time a record is populated. Instead of storing a zero or a blank, which could be interpreted to have meaning, you might store a null value in the field until more information becomes available.
Note
Null values in fields affect 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, the appended fields containing null values are blank, empty, or zero in the destination table.
To permit null values in a field
Open the table in the Table Designer.
On the Fields tab, click in the NULL column for the field.
When null values are permitted for the field, the NULL column displays a check mark for that field. To discontinue permitting null values in the field, click the button in the NULL column so that the check mark is cleared.
For more information, see Fields Tab, Table Designer.
To permit null values in a field programmatically
- When creating the table using the SQL CREATE TABLE command, include the NULL or NOT NULL clauses.
-OR-
- To edit an existing table, open the table with the USE command and then use the SQL ALTER TABLE command with the NULL or NOT NULL clauses.
For more information, see CREATE TABLE - SQL Command and ALTER TABLE - SQL Command.
For example, the following code creates and opens a table that permits null values in one of the fields but not the other two fields:
CREATE TABLE Customer (Cust_ID C(6) NOT NULL, ;
Company C(40) NOT NULL, Contact C(30) NULL)
To permit null values in all fields of the table
Open the table in the Table Designer.
On the Fields tab, click the NULL column for every field.
For more information, see Fields Tab, Table Designer.
To permit null values in all fields of the table programmatically
- Use SET NULL ON before calling the SQL CREATE TABLE command.
Using SET NULL ON also selects the NULL column automatically for each new field you add to the table using the Table Designer and makes it possible for you to not need to include the NULL or NOT NULL clauses in CREATE TABLE.
For more information, see SET NULL Command.
See Also
Tasks
How to: Open Databases
How to: Edit Table Structure
How to: Create Default Field Values