Quickly modify table structure, table record

ALTER table-sql Command

Modify the structure of the table programmatically.
Grammar

ALTER TABLE TableName1
ADD | ALTER [COLUMN] FieldName1
FieldType [(Nfieldwidth [, Nprecision])]
[NULL | Not NULL]
[CHECK LExpression1 [ERROR CMessageText1]]
[DEFAULT EExpression1]
[PRIMARY KEY | UNIQUE]
[REFERENCES TableName2 [TAG TagName1]]
[Nocptrans]
Or
ALTER TABLE TableName1
ALTER [COLUMN] FieldName2
[NULL | Not NULL]
[SET DEFAULT EExpression2]
[SET CHECK LExpression2 [ERROR CMessageText2]]
[DROP DEFAULT]
[DROP CHECK]
Or
ALTER TABLE TableName1
[DROP [COLUMN] FieldName3]
[SET CHECK LExpression3 [ERROR CMessageText3]]
[DROP CHECK]
[ADD PRIMARY KEY eExpression3 TAG TagName2]
[DROP PRIMARY KEY]
[ADD UNIQUE EExpression4 [TAG TagName3]]
[DROP UNIQUE TAG TagName4]
[ADD FOREIGN KEY [eExpression5] TAG TagName4
REFERENCES TableName2 [TAG TagName5]]
[DROP FOREIGN KEY TAG TagName6 [SAVE]]
[RENAME COLUMN FieldName4 to FieldName5]
[Novalidate]

Parameters

TableName1

Specifies the name of the table whose structure you want to modify.

ADD [COLUMN] FieldName1

Specifies the name of the field to add.

ALTER [COLUMN] FieldName1

Specifies the name of an existing field to modify.

FieldType [(Nfieldwidth [, Nprecision]])

Specifies the field type, field width, and field precision (number of digits after the decimal point) for the new field or field to be modified.
A parameter fieldtype is a single character that represents a field data type. Some field types also require parameter nfiledwidth or nprecision, or both.
The following table lists the values of the parameter Filedtype and their corresponding parameters nfiledwidth and Npression trade-offs.

field Type field width Precision description
C N-a character field with a width of n
D--Date
T--Date Time
n n a numeric field with a width of n, with a decimal point reserved for D-bit
F n A floating-point field with a width of n, keep D bit after the decimal point
I--Integral type
B-d Double-precision
Y--Currency type
L--Logical type
M--Memo type
G--General type
For D, T, I, Y, L, M, G, and P-type data, omit the parameters nfieldwidth and nprecision. If the value of the parameter nprecision is not given for N, F, or type B data, the default value is zero.

NULL | Not NULL

Allow or disallow fields to be null values.
If NULL and NOT NULL are omitted, the current set NULL setting determines whether the field is allowed to be empty, and if the command has a PRIMARY KEY or a UNIQUE clause, the current SET NULL setting does not work and the field default is Non-null.

CHECK LExpression1

Specifies the validation rule for the field. LExpression1 is a logical expression value, which can be a user-defined function or stored procedure. Note: Every time you add an empty record, a validation check is performed. If the validation rule does not allow null field values in the added record, Visualfoxpro will produce an error message.

ERROR CMessageText1

Specifies the error message that appears when a field validation check error occurs. This information may appear only if you modify the data in the Browse or edit window.

DEFAULT EExpression1

Specifies the default value for the field. The data type of the EExpression1 must be the same as the data type of the field.

PRIMARY KEY

Create the primary index identity. The index ID has the same name as the field.

UNIQUE

Creates a candidate index identification with the same name as the field. For more information on candidate indexes, see chapter Seventh "Working with tables" in the development Guide.

Note
Candidate indexes (created by the UNIQUE option, with ANSI compatibility in ALTER table or create table). is different from the index created by the index command with the UNIQUE option. Indexes created with the index command with the UNIQUE option allow duplicate index keywords, while candidate indexes do not allow duplicate index keywords.

Null values and duplicate records are not allowed in the primary or candidate index fields.
If you create a new field with the ADD COLUMN clause, Visualfoxpro does not produce an error if you create a primary index or a candidate index for a field that allows null values. However, if you attempt to enter a null or duplicate value into a field that is used as the primary or candidate index, Visualfoxpro generates an error message.
If you modify an existing field and a primary index expression or candidate expression consisting of a field, the field is checked for null or duplicate records. If it is a null or duplicate record, Visual FoxPro generates an error and does not modify the table.

REFERENCES TableName2 TAG TagName1

Specifies the parent table with which to establish a permanent relationship. The parameter TAG TagName1 specifies the parent table index identity, which is based on the parent table index identity. The index ID is up to 10 characters long.

Nocptrans

Prevents code page conversion of a string or Memo field. If the table is converted to another code page, the Nocptrans field is specified without conversion. Nocptrans can only specify character fields and Memo fields.
The following example establishes a table with a name of MYTABLE: The table includes two character fields and two memo fields. The second character field Char2 and the second Memo field Memo2
Contains Nocptrans to prevent conversion.

CREATE TABLE mytable (char1 c), Char2 C (a) Nocptrans;
Memo1 m, Memo2 m Nocptrans)

ALTER [COLUMN] FieldName2

Specifies the name of an existing field to modify.

SET DEFAULT EExpression2

Specifies a new default value for an existing field. The EExpression2 data type must be the same as the field data type.

SET CHECK LExpression2

The word has a new validation rule for the field. The LExpression2 value must be either a logical expression or a user-defined function or an existing procedure.

ERROR cMessageText2

Specifies the error message that is displayed when a validation check error occurs. This information may not be displayed until you change the data in the Browse window or the edit window.

DROP DEFAULT

Deletes the default value for an existing field.

DROP CHECK

Deletes the validation rule for an existing field.

DROP [COLUMN] FieldName3

Deletes a field from the table. Deleting a field also deletes the field's default values and field validation rules.
When a field is deleted, the index keyword or the trigger expression that references the field will become invalid. In this case, deleting the field does not produce an error, but at run time, an invalid index keyword or trigger expression will result in an error.

SET CHECK LExpression3

Specifies the validation rule for a table. LExpression3 must be a logical expression, or it can be a user-defined function or an existing procedure.

ERROR CMESSAGETEXT3

Specifies the error message that is displayed when an error occurs in the validation check for the table. This information can only be displayed if you change the data value in the Browse window or the edit window.

DROP CHECK

Deletes a table's validation rule.

ADD PRIMARY KEY EExpression3 TAG TagName2

Adds a primary index to the table, eexpression specifies the primary index keyword expression, TagName2 specifies the primary index identity name, and the index identity name is up to 10 characters long. If you omit tag
TagName2 while EExpression3 is a field, the primary key index identity is the same as the specified eExpression3.

DROP PRIMARY KEY

Deletes the primary index and its identity. Because a table can have only one primary key, you do not have to specify the name of the keyword. Deleting the primary index also deletes all permanent relationships based on this keyword.

ADD UNIQUE EExpression4 [TAG TagName3]

Adds a candidate index to the table. EEXPRESSION4 Specifies the candidate index keyword expression, TagName3 Specifies the candidate index identification name. The candidate ID name can be up to 10 characters long. If the parameter TAG TagName3 is omitted and EExpression4 is a single field, the candidate index ID has the same name as the specified field in EExpression4.

DROP UNIQUE TAG TagName4

Deletes the candidate index and its identity. Because a table may have multiple candidate keywords, you must specify a candidate index identification name.

ADD FOREIGN KEY [eExpression5] TAG TagName4

Add an external keyword (non-primary) index to the table. EEXPRESSION5 specifies an external index keyword expression, TagName4 specifies the external index identifier name. The index identification name is up to 10 characters long.

REFERENCES TableName2 [TAG TagName5]

Specifies the parent table on which the permanent relationship is created. Use the TAG TagName5 to specify an existing index identifier that establishes a relationship between the table and the parent table based on this index identity. The index identification name can be up to 10 characters long. If you omit the parameter TAG TagName5, the parent table's primary index identity is used to establish the relationship.

DROP FOREIGN KEY TAG TagName6 [SAVE]

Deletes an external keyword that is identified as TagName6 by the index. If the SAVE argument is omitted, the index identity is removed from the structure index. If you add the SAVE parameter, the index identity is not removed from the structure index.

RENAME COLUMN FieldName4 to FieldName5

Allows you to change the field name of a field in a table. FIELDNAME4 Specifies the name of the field to be changed, FIELDNAME5 specifies the new field name.

Attention
Be careful when you change the field name of a table-index expressions, validation rules for fields and tables, commands, functions, and so on may still reference the original field name.

Novalidate

When this option is selected, Visual FoxPro modifies the structure of the table from the data integrity of the table. By default, Visual FoxPro changes the table structure and is constrained by the integrity of the data in the table. Using the Novalidate parameter will invalidate the default condition.

Description
The ALTER TABLE command can be used to modify the structure of a table that has not yet been added to the database. However, when modifying the free table, if you join the DEFAULT, FOREIGN KEY,
PRIMARY REFERENCES or SET clause, Visual FoxPro will receive an error.
The ALTER Table command can rebuild a table by creating a new header and adding records to the header. For example, change the field type or field width.
When a table is rebuilt, field validation rules are executed for all fields that have changed the type or width. If you modify the type or width of all the fields in the table, the table's validation rules are executed.
If you modify a field valid rule or table validation rule for a table that already contains records, Visual FoxPro checks whether the new field or table validation rule matches the existing data and warns you when discrepancies are found.

If the table to be modified is in the database, the ALTER table-sql command requires exclusive use of the database. To open the database exclusively, you can use the Open databases command that contains the EXCLUSIVE clause.

* Example 1 Add a field to the table customer Fax,
* and allows the field to have null values.
* Example 2 makes the cust_id field of the Customer table
* Primary keyword.
* Example 3 adds to the Quantity field in the Orders table
* Validation rules, so that the value of the field quantity is not negative.
* Example 4 based on table customer keyword cust_id and tables
* The candidate keyword in orders cust_id,
* Establish a one-to-many between the customer and the orders
* Permanent relationship.

* Example 5 deletes the Quantity field of the table orders.
* Validation rules.
* Example 6 deletes the permanent relationship between the table customer and the table orders.
* But keep the cust_id index identification in the Orders table.

* Example 1
Set Path to (home () + ' samples/data/') && set path for table
ALTER TABLE customer ADD COLUMN Fax C (a) NULL

* Example 2
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

ALTER TABLE customer ALTER COLUMN cust_id C (5) PRIMARY KEY

* Example 3
ALTER TABLE orders;
ALTER COLUMN quantity SET CHECK quantity >= 0;
ERROR "Quantities must be non-negative"

* Example 4
ALTER TABLE orders;
ADD FOREIGN KEY cust_id TAG cust_id REFERENCES Customer

* Example 5
ALTER TABLE orders ALTER COLUMN quantity DROP CHECK

* Example 6
ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE