Difference between revisions of "CREATE TABLE"

From Recital Documentation Wiki
Jump to: navigation, search
 
(5 intermediate revisions by 2 users not shown)
Line 18: Line 18:
  
 
==See Also==
 
==See Also==
[[ADD TABLE]], [[ALTER INDEX]], [[ALTER TABLE]], [[SQL Constraints|CONSTRAINTS]], [[SQL Data Types|DATA TYPES]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[SQL SELECT|SELECT]], [[SET XMLFORMAT]]
+
[[ADD TABLE]], [[ALTER INDEX]], [[ALTER TABLE]], [[COPY DATABASE]], [[SQL Constraints|CONSTRAINTS]], [[CREATE DATABASE]], [[CREATE INDEX]], [[SQL Data Types|DATA TYPES]], [[DROP DATABASE]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[OPEN DATABASE]], [[SQL SELECT|SELECT]], [[SET XMLFORMAT]]
  
  
Line 60: Line 60:
 
<code lang="recital">
 
<code lang="recital">
 
// Create customer table with column and table constraints
 
// Create customer table with column and table constraints
EXEC SQL
+
CREATE TABLE customer;
    CREATE TABLE customer
+
  (account_no CHAR(5) DESCRIPTION  "Account Code";
      (ACCOUNT_NO char(5) DESCRIPTION  "Account Code"
+
  DEFAULT strzero(seqno(),5),;
      DEFAULT strzero(seqno(),5),
+
  title CHAR(3) DESCRIPTION "Personal Title",
      TITLE char(3) DESCRIPTION "Personal Title",
+
  last_name CHAR(16) DESCRIPTION "Customer's Last Name",;
      LAST_NAME char(16) DESCRIPTION "Customer's Last Name",
+
  first_name CHAR(10) DESCRIPTION "Customer's Given Name",;
      FIRST_NAME char(10) DESCRIPTION "Customer's Given Name",
+
  initial CHAR(2) DESCRIPTION "Customer's Middle Initial",;
      INITIAL char(2) DESCRIPTION "Customer's Middle Initial",
+
  street CHAR(25) DESCRIPTION "Street Number and Name",;
      STREET char(25) DESCRIPTION "Street Number and Name",
+
  city CHAR(12) DESCRIPTION "City",;
      CITY char(12) DESCRIPTION "City",
+
  state CHAR(2) DESCRIPTION "State Abbreviation";
      STATE char(2) DESCRIPTION "State Abbreviation"
+
  CHECK rlookup(customer.state,state);
      CHECK rlookup(customer.state,state)
+
  ERROR "Invalid State",;
      ERROR "Invalid State",
+
  zip CHAR(10) DESCRIPTION "Zip Code",;
      ZIP char(10) DESCRIPTION "Zip Code",
+
  limit DECIMAL(11,2) DESCRIPTION "Credit Limit";
      LIMIT decimal(11,2) DESCRIPTION "Credit Limit"
+
  RECALCULATE,;
      RECALCULATE,
+
  balance DECIMAL(11,2) DESCRIPTION "Credit Balance";
      BALANCE decimal(11,2) DESCRIPTION "Credit Balance"
+
  RECALCULATE,;
      RECALCULATE,
+
  available DECIMAL(11,2) DESCRIPTION "Credit Available";
      AVAILABLE decimal(11,2) DESCRIPTION "Credit Available"
+
  CALCULATED limit-balance,;
      CALCULATED limit-balance,
+
  notes LONG VARCHAR DESCRIPTION "Customer Notes",;
      NOTES LONG VARCHAR DESCRIPTION "Customer Notes",
+
  start_date DATE DESCRIPTION "Customer Start Date";
      START_DATE date DESCRIPTION "Customer Start Date"
+
  DEFAULT date(),;
      DEFAULT date(),
+
  ONOPEN "customer")
      ONOPEN "customer");
+
  
 
// Specify table format as Visual FoxPro
 
// Specify table format as Visual FoxPro
EXEC SQL
+
CREATE TABLE newtable;
    CREATE TABLE newtable
+
  (newid INT AUTOINC,;
      (NEWID int AUTOINC,
+
  newname CHAR(20));
      NEWNAME char(20))
+
  TYPE={VFP}
      TYPE={VFP};
+
  
 
// Create table from XML file
 
// Create table from XML file
EXEC SQL
+
SELECT * FROM customer;
    SELECT * FROM customer
+
  SAVE AS XML cust.xml
      SAVE AS XML cust.xml;
+
  
EXEC SQL
+
CREATE TABLE customer2;
    CREATE TABLE customer2
+
  FROM XML cust.xml
      FROM XML cust.xml;
+
  
 
// Create table from array
 
// Create table from array
Line 106: Line 102:
 
afields(array1)
 
afields(array1)
  
EXEC SQL
+
CREATE TABLE newtable FROM ARRAY array1
    CREATE TABLE newtable FROM ARRAY array1;
+
 
</code>
 
</code>
  
  
 
==Products==
 
==Products==
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
+
Recital Server, Recital  
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:SQL]]
 
[[Category:SQL]]

Latest revision as of 10:37, 28 November 2012

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, COPY DATABASE, CONSTRAINTS, CREATE DATABASE, CREATE INDEX, DATA TYPES, DROP DATABASE, DROP TABLE, GETENV(), INSERT, OPEN DATABASE, 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
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
CREATE TABLE newtable;
  (newid INT AUTOINC,;
  newname CHAR(20));
  TYPE={VFP}
 
// Create table from XML file
SELECT * FROM customer;
  SAVE AS XML cust.xml
 
CREATE TABLE customer2;
  FROM XML cust.xml
 
// Create table from array
set compatible to vfp
use vfptable
afields(array1)
 
CREATE TABLE newtable FROM ARRAY array1


Products

Recital Server, Recital