Creates a table using the specified fields or from an array.

CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE] 
    [CODEPAGE = nCodePage]
    ( FieldName1 FieldType [( nFieldWidth [, nPrecision] )] [NULL | NOT NULL] 
    [CHECK lExpression1 [ERROR cMessageText1]] 
    [AUTOINC [NEXTVALUE NextValue [STEP StepValue]]] [DEFAULT eExpression1] 
    [PRIMARY KEY | UNIQUE [COLLATE cCollateSequence]] 
    [REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS]
    [, FieldName2 ... ] 
    [, PRIMARY KEY eExpression2 TAG TagName2 |, UNIQUE eExpression3 TAG TagName3 
    [COLLATE cCollateSequence]]
    [, FOREIGN KEY eExpression4 TAG TagName4 [NODUP] 
    [COLLATE cCollateSequence] 
    REFERENCES TableName3 [TAG TagName5]] [, CHECK lExpression2 [ERROR cMessageText2]] ) 
    | FROM ARRAY ArrayName

Parameters

CREATE TABLE | DBF TableName1

Creates a table or .dbf. The TableName1 parameter specifies the name of the table. The TABLE and DBF options are identical.

nCodePage

Specifies the code page to use. For a list of code pages, see Code Pages Supported by Visual FoxPro.

NAME LongTableName

Specifies a long name for the table. You can specify a long table name only when a database is open because long table names are stored in databases. Long names can contain up to 128 characters and can be used in place of short file names in the database.

FREE

Specifies that the table is to not be added to an open database. You do not need to use FREE if a database is not open.

FieldName1, FieldType, nFieldWidth,nPrecision

Specifies the field name, field type, field width, and field precision (number of decimal places) respectively. A single table can contain up to 255 fields. If one or more fields allow null values, the limit decreases by one field to 254 fields.

The FieldType parameter is a single letter or long name indicating the field's data type. You can specify nFieldWidth, nPrecision, or both for some field types. The following table lists the values for FieldType and whether you can specify nFieldWidth and nPrecision.

FieldType nFieldWidth nPrecision Data type

W, Blob

-

-

Blob

C, Char, Character

n

Character field of width n

Y, Currency

Currency

D, Date

Date

T, DateTime

DateTime

B, Double

d

Double

G, General

General

I, Int, Integer

Integer

L, Logical

Logical

M, Memo

Memo

N, Num, Numeric

n

d

Numeric field of width n with d decimal places

F, Float

n

d

Floating Numeric field of width n with d decimal places

Q, Varbinary

n

-

Varbinary field of width n

V, Varchar

n

-

Varchar field of width n

The nFieldWidth and nPrecision parameters are ignored for the W, Y, D, T, G, I, L, and M field types. If nPrecision is not included for the N or F types, the nPrecision parameter defaults to zero (no decimal places). If nPrecision is not included for the B type, the nPrecision parameter defaults to the number of decimal places specified by the setting of the SET DECIMALS command.

NULL | NOT NULL

Specifies whether null values are allowed in the field. NULL permits null values, while NOT NULL does not allow null values. If one or more fields can contain null values, the maximum number of fields the table can contain is reduced from 255 to 254.

CHECK lExpression1

Specifies a validation rule for the field. The lExpression1 parameter must evaluate to a logical expression and can be a user-defined function or a stored procedure. Visual FoxPro checks the validation rule specified in the CHECK clause when a blank record is appended.

ERROR cMessageText1

Specifies an error message. Visual FoxPro displays this message when the validation rule specified with the CHECK clause generates an error. The message displays only when data is changed within a Browse window or Edit window.

AUTOINC [NEXTVALUE NextValue [STEP StepValue]]

Enables autoincrementing for the field. NextValue specifies the start value and can be a positive or a negative integer value ranging from -2,147,483,647 to 2,147,483,647. The default value is 1. You can set NextValue using the Next Value spin box in Fields tab of the Table Designer.

StepValue specifies the increment value for the field and can be a positive, nonzero integer value ranging from 1 to 255. The default value is 1. You can set StepValue using the Step spin box in the Fields tab of the Table Designer.

Autoincrementing values cannot be NULL.

NoteNote

Tables containing automatically incrementing field values append table-buffered records approximately 35% slower than tables without automatically incrementing field values, which might affect performance. When using table buffering, the table header is locked when the record is appended.

DEFAULT eExpression1

Specifies a default value for the field specified in FieldName1. The data type of eExpression1 must be the same as the specified field's data type.

If you use the AUTOINC clause to turn on autoincrementing for a field and specify a default value, Visual FoxPro stores the default value in the table but does not use it. Visual FoxPro uses the default value if you use the SQL ALTER TABLE command to remove autoincrementing for the field.

PRIMARY KEY | UNIQUE

PRIMARY KEY creates a primary index for the field specified in FieldName1. UNIQUE creates a candidate index for the field specified in FieldName1. The primary index tag or candidate index tag have the same name as the field. For more information about primary and candidate indexes, see Visual FoxPro Index Types.

COLLATE cCollateSequence

Specifies a collation sequence other than the default setting, MACHINE. The cCollateSequence parameter must be a valid Visual FoxPro collation sequence.

NoteTip

You can also use the SET COLLATE command before creating an index. For more information about setting collation sequences, see Optimizing International Applications and SET COLLATE Command.

REFERENCES TableName2 [TAG TagName1]

Specifies the parent table to which a persistent relationship is established. The parent table cannot be a free table.

The TagName1 parameter clause specifies an index tag name for the parent table in TableName2. Index tag names can contain up to 10 characters. If you omit the TAG clause, the relationship is established using the primary index key of the parent table. If the parent table does not have a primary index, Visual FoxPro generates an error.

NOCPTRANS

Prevents translation to a different code page for Character, Memo, and Varchar fields. You can specify NOCPTRANS only for character and memo fields. This creates what appears to be Character (Binary), Memo (Binary), and Varchar (Binary) data types in the Table Designer.

FieldName2 ...

Specifies one or more additional fields and attributes.

PRIMARY KEY eExpression2 TAG TagName2

Specifies any field or combination of fields in the table for creating a primary index. You cannot use this PRIMARY KEY clause if you previously created a primary index for a field because a table can have only one primary index. If you include more than one PRIMARY KEY clause in a CREATE TABLE statement, Visual FoxPro generates an error.

The TagName2 parameter specifies a name for the primary index tag in eExpression2. Index tag names can contain up to 10 characters.

UNIQUE eExpression3 TAG TagName3

Specifies any field or combination of fields in the table for creating a candidate index. A table can have multiple candidate indexes. However, if you previously created a primary index with one of the PRIMARY KEY options, you cannot include the field that was specified for the primary index.

The TagName3 parameter specifies a name for the candidate index tag in eExpression3. Index tag names can contain up to 10 characters.

FOREIGN KEY eExpression4 TAG TagName4 [ NODUP ]

Creates a foreign (non-primary) index, specifies the index key expression, and establishes a relationship to a parent table. You can create multiple foreign indexes for the table, but foreign index expressions must specify different fields in the table.

The TagName4 parameter specifies the name for the foreign index key tag. Index tag names can contain up to 10 characters.

NODUP creates a candidate foreign index.

REFERENCES TableName3 TAG TagName5

Specifies the parent table to which a persistent relationship is established.

The TagName5 parameter specifies the name of the index tag for the parent table in TableName3 and establishes a relation based on the index tag. Index tag names can contain up to 10 characters. If you omit the TAG clause, the relationship is established by default using the primary index key of the parent table.

CHECK lExpression2

Specifies the table validation rule. The lExpression2 parameter must evaluate to a logical expression and can be a user-defined function or a stored procedure.

ERROR cMessageText2

Specifies an error message for the table validation rule in lExpression2. Visual FoxPro displays this message when the table validation rule generates an error. The message displays only when data is changed within a Browse window or Edit window.

FROM ARRAY ArrayName

Specifies the name of an existing array whose contents are the name, type, precision, and scale for each field in the table. You can use the FROM ARRAY clause instead of specifying individual fields in the SQL CREATE TABLE statement. For the proper format of the contents of the array, see AFIELDS( ) Function.

Autoincrementing is turned on when StepValue is greater than 0.

Expand imageRemarks

Expand imageExamples

Expand imageSee Also