CREATE Table–sql Command

Command CREATE table–sql command
Creates a table has 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 eExpression3 tag TagName3] [, FOREIGN KEY eExpression4 tag TagName4 [nodup] REFERENCES TableName3 [tag TagName5]] [, CHECK LExpression2 [ERROR cMessageText2]] | From ARRAY Arrayname
PARAMETERSTABLENAME1 Specifies 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 the database is open because long table names are stored in databases.
Long names can contain up to 128 characters and can is used in place of short file names in the database.
FREE specifies that the table is not is added to a open database. Free isn ' t required if a database isn ' t open. (FieldName1 FieldType [(Nfieldwidth [, nprecision])] Specifies the field name, field type, field width, and field Precisio N (Number of decimal places), respectively.
A single table can contain up to 255 fields. If one or more fields allow null values, the limit are reduced by one to 254 fields.
FieldType is a single letter indicating the field ' s data type. Some field data types require that for you specify Nfieldwidth or Nprecision, or both.
The following table lists the values for FieldType and whether Nfieldwidth and Nprecision are.
Fieldtypenfieldwidth nprecisiondescriptioncn–character field of width nd––datet––datetimenndnumeric field of width n With the D decimal placesfndfloating numeric field of width n with d decimal placesi––integerb–ddouble y––currencyl––logicalm– –memog––general
Nfieldwidth and Nprecision are ignored for D, T, I, Y, L, M, G, and P types. Nprecision defaults to zero (no decimal places) if nprecision isn ' t included for the N or F types. Nprecision defaults to the number of decimal places specified by the SET decimal setting if nprecision isn ' t included for The B type.
NULL allows null values in the field. If one or more fields can contain null values, the maximum number of fields the table can contain be reduced by one, from 255 to 254. Not NULL prevents null values in the field.
If you are omit null and NOT NULL, the current setting to SET NULL determines if null values are allowed in the field. However, if you are omit null and NOT NULL and include the PRIMARY KEY or a UNIQUE clause, the current setting of a SET NULL is IG Nored and the field defaults to not NULL.
CHECK lExpression1 Specifies a validation rule for the field. LExpression1 can be a user-defined function. Note this when a blank the "appended", the validation is checked. An error was generated if the validation rule doesn ' t allow for a blank field value in a appended record. Error CMESSAGETEXT1 Specifies the error message Visual FoxPro displays when the validation rule specified with CHECK Gener Ates an error. The message was displayed only when data is changed within 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 KEY creates 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, 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 with the unique option In the INDEX command. An index created with the UNIQUE option in the index command allows duplicate the index keys; Candidate indexes does not allow duplicate index keys. The INDEX for additional information in its UNIQUE option.
Null values and duplicate records are not permitted in a field used for a primary or candidate index. However, Visual FoxPro won't generate an error if you create a primary or candidate index for a field that supports NUL L values. Visual FoxPro'll generate an error if you attempt to enter a null or duplicate value into a field used for a primary or Candidate Index.
REFERENCES TableName2 [TAG TagName1] Specifies the parent table to which a persistent relationship is established. If you are 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 based on a existing index TAG for the parent table. Index tag names can contain up to characters.
The parent table cannot is a free table.
Nocptrans prevents translation to a different The code page for character and Memo fields. If the table is converted to another code page, the fields for which Nocptrans has been the specified are. Nocptrans can is specified for character and Memo fields. This would 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. The second character field CHAR2 and the second Memo field MEMO2 include Nocptrans to prevent translation.
CREATE TABLE MyTable (char1 C (), Char2 C () 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 TagName2 Specifies the name for the primary index tag this is created. Index tag names can contain up to characters.
Because a table can have only one primary index, and cannot include this clause if to you have already the created a primary Inde X 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 are have created a primary index with one of the primary KEY options, you cannot include the field this was SP Ecified for the primary index. Tag TagName3 specifies a tag name for the candidate index tag this is created. Index tag names can contain up to characters.
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 P Arent table. EEXPRESSION4 Specifies the foreign index key expression and TagName4 specifies the name of the foreign index key tag that is created. Index tag names can contain up to characters. Include Nodup to create a candidate foreign index.
Can create multiple foreign indexes for the table, but the foreign index expressions must specify different fields in The table.
REFERENCES TableName3 [TAG TagName5] Specifies the parent table to which a persistent relationship is established. Include tag TagName5 to establish a relation based on a index TAG for the parent table. Index tag names can contain up to characters. If you omit TAG TagName5, the relationship are established using the parent table ' s primary index key by default. CHECK EExpression2 [ERROR cMessageText2] Specifies the table validation rule. The error CMESSAGETEXT2 specifies the "error message" Visual FoxPro displays when the table validation the rule is executed. The message was displayed only when data is changed within a Browse window or Edit window. From array arrayname Specifies the name of a existing ARRAY whose contents are the name, type, precision, and scale for E Ach field in the table. The contents of the array can is defined with the Afields () function. Remarks
The new table is opened into the lowest available work area, and can are accessed by its alias. The new table is opened exclusively, regardless of the current setting of the SET EXCLUSIVE.

If A is open and don ' t include the clause, the new table was added to the database. You are cannot create a new table with the same name as a table in the database.

If a database isn ' t open when you create the new table, including the NAME, CHECK, DEFAULT, FOREIGN key, PRIMARY key, or R Eferences clauses generates an error.

Note This CREATE TABLE syntax uses commas to separate certain CREATE table options. Also, the null, NOT NULL, CHECK, DEFAULT, PRIMARY KEY and UNIQUE clause must is placed within the parentheses containing T He column 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 bet Ween 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 the data into specific fields. The MODIFY DATABASE is used to display the relationship between the three tables.

Close databasesclear* Create MyData database in the current directory or FolderCreate database mydata1* create a Salesman Table with a primary keycreate table salesman; (Salesid C (6) PRIMARY KEY,; Salename C) * Create a Customer table and relate it to the salesman table. CREATE TABLE Customer; (Salesid C (6),; CustID i PRIMARY KEY,; CustName C (a) UNIQUE,; Salesbranch C (3),; FOREIGN key Salesid TAG salesid REFERENCES salesman) * Create an Orders table related to the 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 is at least 10"; Discpercent N (6,2) NULL; DEFAULT. NULL.,; CHECK (Orderamt > 0) ERROR "Order Amount must is > 0") * Display new database, tables, and Relationshipsmodify Datab ase* Delete Example Filesset SAFETY off && to suppress veRification messageclose DATABASES && Close database before Deletingdelete database MyData1 deletetables