CREATE TABLE - SQL Command
Creates a Table Having The Specified Fields.
Create Table | DBF Tablename1 [Name Longtablename] [Free]
(FieldName1 FieldType [(nfieldwidth [, nprecision])]
[NULL | NOT NULL] [Check Lexpression1 [Error CMessageText1]]]
[Default eExpression1] [PRIMARY Key | Unique]
[References Tablename2 [Tag Tagname1]] [NOCPTRANS]
[, FieldName2 ...] [, PRIMARY Key EEXPRESSION2 TAG TAGNAME2
Unique EEXPIESSION3 TAG TAGNAME3]
[, Foreign Key EEXPRESSION4 TAG TAGNAME4 [NODUP] References Tablename3 [Tag Tagname5]]]]
[, Check Lexpression2 [Error CMessageText2]]) | from array arrayname
Parameters
Tablename1
Specifier the name of the table to create. The table and dbf options are identical.
Name
Longtablename
Specifies a long name for the table. A long table name can be specified Only WHEN A DATABASE IS OPEN BECAUSE Long Table Names Are Stored in Databases.
LONG NAMES CAN Contain Up to 128 Characters and Can Be Used in Place of Short File Names in The Database.
Free
Specifies That The Table Will Not Be Added To An Open Database. Free Isn't Required IF A Database ISN 'Open.
(FieldName1 FieldType "
NfieldWidth [,
npRecision])])])])])
Specifier The Field Name, Field Type, Field Width, And Field Precision (Number of DeciMal Places), Respectively.
A Single Table Can Contain Up to 255 Fields. If one or more fields allow null values, the limit is reduced by one to 254 fields.
FieldType Is A Single Letter Indicating The Field's Data Type. Some Field Data Types Require That You Specify NfieldWidth OR NPRECISION, OR Both.
The Following Table Lists The Values for FieldType and WHether NfieldWidth and NPRISION Are Required.
FieldTypenFieldWidth nPrecisionDescriptionCn-Character field of width nD - DateT - DateTimeNndNumeric field of width n with d decimal placesFndFloating numeric field of width n with d decimal placesI - IntegerB-dDouble Y - CurrencyL - LogicalM - MemoG - GeneralnFieldWidth and NPRECISION Area Ignored for D, T, I, Y, L, M, G, AND P Types. nprecision defaults zer (no decimal place) if nprecision isn't incruded for the n or f types. nprecision defaults to the number of Decimal Places Specified by The Set DeciMal Setting IF NPRECISION ISN 'inCluded for the B Type.
NULL
Allows null values in the field. If one or more fields can net number of fields the table can contact is reduced by one, from 255 to 254.
NOT NULL
Prevents null values in the field.
If you omit NULL and NOT NULL, the current setting of SET NULL determines if null values are allowed in the field. However, if you omit NULL and NOT NULL and include the PRIMARY KEY or UNIQUE clause, the current setting of SET NULL is ignored And the field defaults to not null.
CHECK
LEXPRESSION1
Specifies a validation rule for the field.
lExpression1 can be a user-defined function. Note that when a blank record is appended, the validation rule is checked. An error is generated if the validation rule does not allow for a blank field value in an appended record.
Error
CMessageText1
Spectation rule specified with check generates an error. The message is displayed Only When Data I. Changed Withnin A Browse Window OR Edit Window.
DEFAULT
EEXPRESSION1
Specifies a Default Value for the field. The Data Type of
EEXPRESSION1Must Be The Same As The Field's Data Type.
Primary Keycreates a primary index for the field. The primary index tag Has the Same name as the field.
Unique
Creates a Candidate Index for the Field. The Candidate Index Tag Has The Same Name As The Field. For more information about Candidate Indexes, See
Setting a primary or candidate index.
Note Candidate indexes (created by including the UNIQUE option in CREATE TABLE or ALTER TABLE - SQL). Are not the same as indexes created with the UNIQUE option in the INDEX command An index created with the UNIQUE option in the INDEX command allows duplicate index keys Candidate Indexes Do Not ALLOW DUPLICATE INDEX KEYS. See
Index for Additional Information ON ITS UNIQUE OPTION.
Null values and duplicate records are not permitted in a field used for a primary or candidate index. However, Visual FoxPro will not generate an error if you create a primary or candidate index for a field that supports null values. Visual FoxPro will generate an error IF you attempt to enter a null or duplicate value Into a field subsid..
References
Tablename2 [tag
Tagname1]
Specifies the Parent Table to Which a Persistent Relationship Is Established. If omit tag
TAGNAME1, THE RELATIONSHIP IS ESTABLISHED USING The PRIMARY INDEX Key of The Parent Table. If The Parent Table Does Not Have A Primary Index, Visual FoxPro Generates An Error.
INCLUDE TAG TAGNAME1 TO ESTABLISH A RELATION BASTAN AN EXISTINDEX TAG for the Parent Table. Index Tag Names Can Contain Up to 10 Characters.
The Parent Table Cannot Be a free table.
Nocptrans
Prevents translation to a different code page for character and memo fields. If the table is converted to another code page, the fields for which NOCPTRANS has been specified are not translated. NOCPTRANS can only be specified for character and memo fields. This will create what appears in the Table Designer as Character (binary) and Memo (binary) data types.The following example creates a table named MYTABLE containing two character fields and two memo fields. The second character field CHAR2 and the second memo field MEMO2 include NOCPTRANS to prevent TRANSLATION.
Create Table MyTable (Char1 C (10), Char2 C (10) NOCPTRANS ,;
MEMO1 M, MEMO2 M NOCPTRANS)
PRIMARY Key
EEXPRESSION2 TAG
TAGNAME2
Specifies a primary index to create.
EEXPRESSION2 Specifies Any Field or Combination of Fields in The TABLE. TAG
.
Because a table can have only one primary index, you can not include this clause if you have already created a primary index for a field. Visual FoxPro generates an error if you include more than one PRIMARY KEY clause in CREATE TABLE.
Unique
EEXPRESSION3 TAG
TAGNAME3
Creates a Candidate INDEX.
eExpression3 specifies any field or combination of fields in the table. However, if you have created a primary index with one of the PRIMARY KEY options, you can not include the field that was specified for the primary index. TAG
.
A Table Can Have Multiple Candidate Indexes.
Foreign Key
EEXPRESSION4 TAG
Tagname4 [NODUP]
Creates A Foreign (Non-Primary) Index, And Establishes a Relationship To a Parent Table.eExpression4 Specifies The Foreign Index Key Expression and
Tagname4 specifies the name of the foreign index key tag thing is created
INDEX TAG NAMES CAN Contain Up to 10 Characters. Include Nodup To Create A Candidate Foreign Index.
You can create Multiple Foreign INDEXES for the Table, But The Foreign Index Expressions Must Specify Different Fields in The Table.
References
Tablename3 [tag
Tagname5]
Specifier the Parent Table to Which a Persistent Relationship Is Established. Include Tag
Tagname5 to establish a rellation based on an index tag for the parent Table. Index Tag names can contact up to 10 character tag. IF you omit tag
.
CHECK
EEXPRESSION2 [Error
CMessageText2]
Specifies The Table Validation Rule. Error
.
From array
ArrayName
Specifier the name of an existing array whose contents are the name, type, precision, and scale for each field in the table. The contents of the array can be defined with the afields () function.
Remarks
...........................
IF A Database IS Open and you don't include the free clause, the new table is added to the database. You Cannot Create a New Table I The Same Name As a Table In The Database.
If a database is not open when you create the new table, including the NAME, CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY, or REFERENCES clauses generates an error.Note that the CREATE TABLE syntax uses commas to separate certain CREATE TABLE options. Also, The Null, Not Null, Check, Default, Primary Key and Unique Clause Must Be Placed Wtemn Definitions.
EXAMPLE
The following example creates a new database named Mydata1. CREATE TABLE is used to create three tables (Salesman, Customer, and Orders). The FOREIGN KEY and REFERENCES clauses in the second CREATE TABLE command create a persistent one-to-many relationship between the Salesman and Customer tables. The dEFAULT clauses in the third CREATE TABLE command establish default values, and the CHECK and ERROR clauses establish business rules for entering data into specific fields. The MODIFY DATABASE is used to display the relationship between the three tables.
Close Databases
Clear
* Create MyData Database In The Current Directory or Folder
Create Database MyData1
* CREATE A SALESMAN TABLE with A PRIMARY Key
Create Table Salesman;
(Salesid C (6) Primary Key,;
Salename C (20))
* CREATE A Customer Table and Relate It to the Salesman Table.
Create Table Customer;
(SalesID C (6),;
Custid I Primary Key,;
CustName C (20) Unique,;
Salesbranch C (3),;
Foreign Key Salesid Tag Salesid References SaleSman)
* Create An ORDERS TABLE RELATED TO Customer with ITS OWN Primary
* Key and some business rules such as defaults & checks.
Create Table ORDERS;
(ORDERID I PRIMARY Key,;
Custid I References Customer Tag CustId,;
Orderamt y (4),;
ORDERQTY I;
DEFAULT 10;
CHECK (OrderQty> 9);
Error "Order Quantity Must Be at Least 10", DiscPercent N (6, 2) NULL;
DEFAULT. Null.,;
Check (Orderamt> 0) Error "Order Amount Must Be> 0")
* Display New Database, Tables, And RELATIONSHIPS
Modify Database
* Delete EXAMPLE Files
Set Safety Off && To Suppress Verification Message
Close Databases && Close Database Before Deleting
Delete Database MyData1 deleteTables
