CREATE TABLE

Class

SQL Applications

Purpose

Creates a table

Syntax

CREATE [TEMPORARY] TABLE | DBF [IF NOT EXISTS] [<database>!]<table>

[NAME <LongTableName>]

[FREE] (<column> <datatype> [<column constraint> [...]][,...] [<table constraint> [...]])

[TYPE=CLIPPER | CLIPPER5 | RECITAL | DBASE3 | DBASE4 | FOXPLUS | FOXPRO | VFP]

| [FROM] XML <.xml file> [LOAD] | FROM ARRAY <array>

See Also

ADD TABLE, ALTER INDEX, ALTER TABLE, CONSTRAINTS, DATA TYPES, DROP TABLE, GETENV(), INSERT, SELECT, SET XMLFORMAT

Description

The CREATE TABLE and CREATE DBF commands are synonymous. Each creates a new table in the current database. The INSERT command can be used to populate the table with data. The ALTER TABLE command is used to change the table definition once it is created. CREATE INDEX can also be used to add new indexes and DROP INDEX to remove existing ones.

Keywords	Description
TEMPORARY	The table is created as a temporary table for this process and will be deleted when the process terminates.
IF NOT EXISTS	The table is only created if it does not already exist. An error occurs if the table already exists and the IF NOT EXISTS clause is not specified.
database	The name of the database in which the table should be created. Databases in Recital are implemented as directories containing files that correspond to the tables and associated files in the database. Operating System file protection can be applied individually to the files for added security. The directory is a sub-directory of the Recital data directory. The environment variable / symbol DB_DATADIR points to the current Recital data directory and can be queried using the GETENV() function. Files from other directories can be added to the database using the ADD TABLE command or via the database catalog and SET AUTOCATALOG functionality. The '!' character must be included between the database name and the table name.
table	The name of the table to be created.
NAME<LongTableName>	Specify an alternative name for the table.
FREE	Specify that the table is not to be added to the currently active database.
column	This is the name of the column to be created.
datatype	The data type to be stored in that column, and applicable length and precision. See the data types section for additional information.
column constraint	A column constraint.
table constraint	A table constraint.
CLIPPER5 | RECITAL | DBASE3 | DBASE4 | FOXPLUS | FOXPRO | VFP	The table's file format. The default is RECITAL or the current SET FILETYPE format if set.
[FROM] XML <.xml file> [LOAD]	The table structure is taken from the specified XML file. If the LOAD option is specified any data in the xml file is loaded into the newly created table. The XML file must be in ADO (Microsoft® ActiveX® Data Objects) format.
FROM ARRAY <array>	The table structure is taken from an existing array, whose name is specified in <array>. The array contents must be the column name, type, precision and scale for each column in the new table structure.

Example

// Create customer table with column and table constraints
EXEC SQL
    CREATE TABLE customer
      (ACCOUNT_NO char(5) DESCRIPTION  "Account Code"
      DEFAULT strzero(seqno(),5),
      TITLE char(3) DESCRIPTION "Personal Title",
      LAST_NAME char(16) DESCRIPTION "Customer's Last Name",
      FIRST_NAME char(10) DESCRIPTION "Customer's Given Name",
      INITIAL char(2) DESCRIPTION "Customer's Middle Initial",
      STREET char(25) DESCRIPTION "Street Number and Name",
      CITY char(12) DESCRIPTION "City",
      STATE char(2) DESCRIPTION "State Abbreviation"
      CHECK rlookup(customer.state,state)
      ERROR "Invalid State",
      ZIP char(10) DESCRIPTION "Zip Code",
      LIMIT decimal(11,2) DESCRIPTION "Credit Limit"
      RECALCULATE,
      BALANCE decimal(11,2) DESCRIPTION "Credit Balance"
      RECALCULATE,
      AVAILABLE decimal(11,2) DESCRIPTION "Credit Available"
      CALCULATED limit-balance,
      NOTES LONG VARCHAR DESCRIPTION "Customer Notes",
      START_DATE date DESCRIPTION "Customer Start Date"
      DEFAULT date(),
      ONOPEN "customer");
 
// Specify table format as Visual FoxPro
EXEC SQL
    CREATE TABLE newtable
      (NEWID int AUTOINC,
      NEWNAME char(20))
      TYPE={VFP};
 
// Create table from XML file
EXEC SQL
    SELECT * FROM customer
      SAVE AS XML cust.xml;
 
EXEC SQL
    CREATE TABLE customer2
      FROM XML cust.xml;
 
// Create table from array
set compatible to vfp
use vfptable
afields(array1)
 
EXEC SQL
    CREATE TABLE newtable FROM ARRAY array1;

Products

Recital Database Server, Recital Mirage Server, Recital Terminal Developer