Difference between revisions of "CREATE TABLE"
From Recital Documentation Wiki
Yvonnemilne (Talk | contribs) (→Example) |
Yvonnemilne (Talk | contribs) |
||
| (2 intermediate revisions by one user not shown) | |||
| 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]], [[CREATE INDEX]], [[SQL Data Types|DATA TYPES]], [[DROP DATABASE]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[OPEN DATABASE]], [[SQL SELECT|SELECT]], [[SET XMLFORMAT]] |
| Line 61: | Line 61: | ||
// Create customer table with column and table constraints | // Create customer table with column and table constraints | ||
CREATE TABLE customer; | CREATE TABLE customer; | ||
| − | ( | + | (account_no CHAR(5) DESCRIPTION "Account Code"; |
DEFAULT strzero(seqno(),5),; | 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); | CHECK rlookup(customer.state,state); | ||
ERROR "Invalid State",; | ERROR "Invalid State",; | ||
| − | + | zip CHAR(10) DESCRIPTION "Zip Code",; | |
| − | + | limit DECIMAL(11,2) DESCRIPTION "Credit Limit"; | |
RECALCULATE,; | RECALCULATE,; | ||
| − | + | balance DECIMAL(11,2) DESCRIPTION "Credit Balance"; | |
RECALCULATE,; | RECALCULATE,; | ||
| − | + | available DECIMAL(11,2) DESCRIPTION "Credit Available"; | |
CALCULATED limit-balance,; | CALCULATED limit-balance,; | ||
| − | + | notes LONG VARCHAR DESCRIPTION "Customer Notes",; | |
| − | + | start_date DATE DESCRIPTION "Customer Start Date"; | |
DEFAULT date(),; | DEFAULT date(),; | ||
ONOPEN "customer") | ONOPEN "customer") | ||
| Line 86: | Line 86: | ||
// Specify table format as Visual FoxPro | // Specify table format as Visual FoxPro | ||
CREATE TABLE newtable; | CREATE TABLE newtable; | ||
| − | ( | + | (newid INT AUTOINC,; |
| − | + | newname CHAR(20)); | |
TYPE={VFP} | TYPE={VFP} | ||
| Line 104: | Line 104: | ||
CREATE TABLE newtable FROM ARRAY array1 | CREATE TABLE newtable FROM ARRAY array1 | ||
</code> | </code> | ||
| + | |||
==Products== | ==Products== | ||
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
