CREATE TABLE

From Lianjapedia
Jump to: navigation, search

Purpose

Creates a table

Syntax

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

[NAME <LongTableName>]

[FREE]

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

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

| FROM ARRAY <array>

See Also

ADD TABLE, ALTER INDEX, ALTER TABLE, ALTER VIRTUALTABLE, COLUMNMETADATA(), COPY DATABASE, CONSTRAINTS, CREATE DATABASE, CREATE VIRTUALTABLE, Custom Builders, DATA TYPES, DATABASEMETADATA(), DROP DATABASE, DROP TABLE, GETENV(), INSERT, METADATA_DECODE(), METADATA_ENCODE(), METADATA_FINDTYPE(), OPEN DATABASE, SELECT, SET XMLFORMAT, TABLEMETADATA(), TABLEMETADATAVERSION()

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. 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. A table can have a maximum of 256 columns.
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.
metadata A metadata string.
[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
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())
 
// Create table from XML file
SELECT * FROM customer;
  SAVE AS XML cust.xml
 
CREATE TABLE customer2;
  FROM XML cust.xml
 
// Create table from array
use oldtable
afields(array1)
 
CREATE TABLE newtable FROM ARRAY array1