Share via

CREATE TABLE - SQL Command

  • Article

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

CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE] 
    ( 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.

  • 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 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 indicating the field's data type. Some field data types require that you specify nFieldWidth or nPrecision, or both. The following table lists the values for FieldType and whether nFieldWidth and nPrecision are required.

    FieldType nFieldWidth nPrecision Data type
    C n – Character field of width n
    D – – Date
    T – – DateTime
    N n d Numeric field of width n with d decimal places
    F n d Floating numeric field of width n with d decimal places
    I – – Integer
    B – d Double
    Y – – Currency
    L – – Logical
    M – – Memo
    G – – General

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

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

    Note   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 ALTER TABLE - SQL 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 Setting a Primary or Candidate Index.

  • COLLATE cCollateSequence
    Specifies a collation sequence other than the default setting, MACHINE. The cCollateSequence parameter must be a valid Visual FoxPro collation sequence. For more information about setting collation sequences, see Optimization of 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 and memo fields. You can specify NOCPTRANS only for character and memo fields. This creates what appears to be Character (binary) and Memo (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 CREATE TABLE - SQL statement. For the proper format of the contents of the array, see AFIELDS( ) Function.

    Autoincrementing is turned on when StepValue is greater than 0.

Remarks

The new table opens in the lowest numbered available (unused) work area and can be accessed by its alias. The new table opens exclusively, regardless of the current setting of the SET EXCLUSIVE command.

If a database is open and you do not include the FREE clause, the new table is added to the database. You cannot create a new table with the same name as a table in the database.

If the table is converted to another code page, the fields for which NOCPTRANS has been specified are not translated.

If a database is not open when you create the new table, including the NAME, CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY, or REFERENCES clauses generates an error.

Tables created in the Visual FoxPro OLE DB Provider using CREATE TABLE are placed in the default folder of the calling application, unless you specify another location.

**Note   **The CREATE TABLE syntax uses commas to separate certain CREATE TABLE options. You must place the NULL, NOT NULL, CHECK, DEFAULT, PRIMARY KEY, and UNIQUE clauses within the parentheses containing the column definitions.

If you omit NULL and NOT NULL, the current setting of the SET NULL command determines if null values are allowed in the field. However, if you omit NULL and NOT NULL but include the PRIMARY KEY or UNIQUE clause, Visual FoxPro disregards the current setting of SET NULL, and the field defaults to NOT NULL.

Visual FoxPro generates an error if the validation rule specified in the CHECK clause does not allow for a blank field value in an appended record.

Null values and duplicate records are not permitted in a field used for a primary or candidate index. However, Visual FoxPro does not generate an error if you create a primary or candidate index for a field that supports null values. Visual FoxPro generates an error if you attempt to enter a null or duplicate value into a field used for a primary or candidate index.

Note   Candidate indexes, created by including the UNIQUE option (provided for ANSI compatibility) in CREATE TABLE – SQL or ALTER TABLE – SQL commands, are not the same as indexes created in the INDEX command with the UNIQUE option. An index created in the INDEX command using the UNIQUE option allows duplicate index keys; candidate indexes do not allow duplicate index keys. For more information about the UNIQUE option in the INDEX command, see INDEX Command.

For NextValue, an empty value is interpreted as 0 and causes incrementing to begin with 0 + StepValue.

The StepValue incremental value is always positively added. If you want to use a negative automatically incrementing series, you should begin with a negative NextValue and step towards 0 using the StepValue increment value. For example, if NextValue equals -2147483647, the first step produces a value of -2147483646.

CREATE TABLE recognizes all the fields available in the AFIELDS( ) function and turns on autoincrementing in the table when StepValue is greater than zero.

Examples

Example 1

The following example creates a new database named mydata1 and uses CREATE TABLE to create three tables: Salesman, Customer, and Orders. The FOREIGN KEY and REFERENCES clauses in the second CREATE TABLE command create a persistent one-to-many relationship between the Salesman and Customer tables. The DEFAULT clauses in the third CREATE TABLE command establish default values, and the CHECK and ERROR clauses establish business rules for entering data into specific fields. The MODIFY DATABASE command displays the relationship between the three tables.

CLOSE DATABASES
CLEAR
CREATE DATABASE mydata1

* Create a Salesman table with a primary key.
CREATE TABLE Salesman ;
   (SalesID c(6) PRIMARY KEY, ;
   SaleName C(20))

* Create a Customer table and relate it to the Salesman table.
CREATE TABLE Customer ;
   (SalesID c(6), ;
   CustId i PRIMARY KEY, ;
   CustName c(20) UNIQUE,   ;
   SalesBranch c(3), ;
   FOREIGN KEY SalesId TAG SalesId REFERENCES Salesman)

* Create an Orders table related to Customer with its own primary
* key and some business rules such as defaults and checks.
CREATE TABLE Orders ;
   (OrderId i PRIMARY KEY, ;
      CustId i REFERENCES customer TAG CustId, ;
      OrderAmt y(4), ;
      OrderQty i ;
      DEFAULT 10 ;
      CHECK (OrderQty > 9) ;
      ERROR "Order Quantity must be at least 10", ;
         DiscPercent n(6,2) NULL ;
      DEFAULT .NULL., ;
      CHECK (OrderAmt > 0) ERROR "Order Amount must be > 0" )

* Display new database, tables, and relationships.
MODIFY DATABASE

* Delete example files.
SET SAFETY OFF && Suppress verification message.
CLOSE DATABASES     && Close database before deleting.
DELETE DATABASE mydata1 DELETETABLES

Example 2

The following example uses NOCPTRANS to prevent translation to a different code page. The example creates a table named "mytable" that contains two character fields and two memo fields. The second character field, "char2", and the second memo field, "memo2", include NOCPTRANS to prevent translation.

CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,;
   memo1 M, memo2 M NOCPTRANS)

Example 3

The following example creates a Customer table with a field called myField that has Integer data type and uses automatically incrementing field values:

CREATE TABLE Customer (myField i AUTOINC NEXTVALUE 1 STEP 1, name c(40) )

See Also

AFIELDS( ) Function | ALTER TABLE – SQL Command | Autoincrementing Field Values in Tables | CREATE Command | CREATE QUERY | INSERT – SQL Command | MODIFY QUERY Command | MODIFY STRUCTURE Command | OPEN DATABASE Command | SELECT – SQL Command | SET NOCPTRANS Command | SQL Commands Overview