CREATE TABLE - SQL Command
Creates a table having the specified fields.
The Visual FoxPro ODBC Driver supports the native Visual FoxPro language syntax for this command. For driver-specific information, see Driver Remarks.
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE] (FieldName1FieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1] [PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS] [, FieldName2 ...] [, PRIMARY KEY eExpression2 TAG TagName2 |, UNIQUE eExpression3 TAG TagName3] [, FOREIGN KEY eExpression4 TAG TagName4 [NODUP] REFERENCES TableName3 [TAG TagName5]] [, CHECK lExpression2 [ERROR cMessageText2]]) | FROM ARRAY ArrayName
The new table is opened in the lowest available work area and can be accessed by its alias. The new table is opened exclusively, regardless of the current setting of SET EXCLUSIVE.
If a database is open and you don't 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 a database is open, CREATE TABLE - SQL requires exclusive use of the database. To open a database for exclusive use, include EXCLUSIVE in OPEN DATABASE.
If a database isn't open when you create the new table, including the NAME, CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY, or REFERENCES clauses generates an error.
CREATE TABLE syntax uses commas to separate certain CREATE TABLE options. Also, the NULL, NOT NULL, CHECK, DEFAULT, PRIMARY KEY, and UNIQUE clause must be placed within the parentheses containing the column definitions.
When your application sends the ODBC SQL statement CREATE TABLE to the data source, the Visual FoxPro ODBC Driver translates the command into the Visual FoxProCREATE TABLE command using the syntax shown in the following table.
Visual FoxPro syntax
CREATE TABLE base-table-name
(column-identifier data type
[,column-identifier data type
[NOT NULL] ...)
CREATE TABLE TableName1 [NAME LongTableName]
[(nFieldWidth [, nPrecision])]
When you create a table using the driver, the driver closes the table immediately after creation to allow access to the table by other users. This differs from Visual FoxPro, which leaves the table open exclusively upon creation. However, if a stored procedure on your data source containing a CREATE TABLE statement executes, the table is left open.
If the data source is a database (.dbc file), the Visual FoxPro ODBC Driver creates a table named LongTableName with the same name as the base-table-name.
Using Data Definition Language (DDL)
You cannot include DDL in the following places:
In a batch SQL statement that requires a transaction
In manual-commit mode, after a statement that required a transaction, unless your application first calls SQLTransact.
For example, if you want to create a temporary table, you should create the table before you begin the statement requiring a transaction. If you include the CREATE TABLE statement in a batch SQL statement that requires a transaction, the driver returns an error message.