Difference between revisions of "CREATE TABLE"
From Recital Documentation Wiki
Yvonnemilne (Talk | contribs) (→Example) |
Yvonnemilne (Talk | contribs) |
||
| Line 18: | Line 18: | ||
==See Also== | ==See Also== | ||
| − | [[ADD TABLE]], [[ALTER INDEX]], [[ALTER TABLE]], [[COPY DATABASE]], [[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]], [[SQL Data Types|DATA TYPES]], [[DROP DATABASE]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[OPEN DATABASE]], [[SQL SELECT|SELECT]], [[SET XMLFORMAT]] |
| Line 104: | Line 104: | ||
CREATE TABLE newtable FROM ARRAY array1 | CREATE TABLE newtable FROM ARRAY array1 | ||
</code> | </code> | ||
| + | |||
==Products== | ==Products== | ||
Revision as of 10:19, 22 December 2009
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, 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
