ALTER TABLE TableName1 ADD | ALTER [COLUMN] FieldName1

FieldType [( nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]]

p>

[AUTOINC [NEXTVALUE NextValue [STEP StepValue]]] [DEFAULT eExpression1]

[primary key | UNIQUE [COLLATE cCollateSequence]]

[REFERENCES TableName2 [TAG TagName1 ]] [NOCPTRANS] [NOVALIDATE]

or

ALTER TABLE TableName1 ALTER [COLUMN] FieldName2 [NULL | NOT NULL] [SET DEFAULT eExpression2]

[ SET CHECK lExpression2 [ERROR cMessageText2]] [ DROP DEFAULT ] [ DROP CHECK ] [ NOVALIDATE ]

or

ALTER TABLE TableName1 [DROP [COLUMN] FieldName3]

[SET CHECK lExpression3 [ERRORcMessageText3]] [DROP CHECK]

[ADD primary key eExpression3 [FOR lExpression4] TAG TagName2

[, COLLATE cCollateSequence]] [DROP primary key] < /p>

[ADD UNIQUE eExpression4 [[FOR lExpression5] TAG TagName3

[ COLLATE cCollateSequence]]] [DROP UNIQUE TAG TagName4]

[ADD foreign key [eExpression5] [FOR lExpression6] TAG TagName4

[ COLLATE cCollateSequence] REFERENCES TableName2 [TAG TagName5]]

[DROP foreign key TAG TagName6 [SAVE]] [RENAME COLUMN FieldName4 TO FieldName5] [NOVALIDATE ]

Parameters

ALTER TABLE TableName1

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

ADD | ALTER [COLUMN] FieldName1

Specify the field name to be added.

A single table can have up to 255 fields. If one or more fields allow null values, the limit is 254 fields.

FieldType [(nFieldWidth [, nPrecision])]

Specify the field type, field width and field precision (number of digits after the decimal point) of the new field or the field to be modified.

The parameter FieldType is a single character representing the field data type. Some field types also require parameters nFiledWidth or nPrecision, or both.

The following table lists the value of the parameter FiledType and the trade-offs of the corresponding parameters nFiledWidth and nPression: Field type field width precision description

Cn – character field with width n

D– –Date

T––Date time

Nnd is a numeric field with a width of n, retaining d digits after the decimal point

Fnd is a floating-point field with a width of n Field, retaining d digits after the decimal point

I––integer type

B–d double type

Y––currency type

L––Logical type

M––Comment type

G––General type

P––Image

For For D, T, I, Y, L, M, G and P type data, the parameters nFieldWidth and nPrecision are omitted. If no value is given for the parameter nPrecision for type N, F, or B data, its default value is zero.

NULL | NOT NULL

Allow or disallow null values ??for fields. NULL allows null values, NOT NULL does not allow null values. If one or more fields allow null values, the number of fields that can be included in the table is reduced from 255 to 254.

CHECK lExpression1

Specify the validity rules of the field. lExpression1 is a logical expression value, which can be a user-defined function or stored procedure. NOTE: A validity check will be done every time an empty record is added.

ERROR cMessageText1

Specify the error message displayed when an error occurs in the field validity check. This information may only appear when data is modified in the Browse or Edit window.

AUTOINC [NEXTVALUE NextValue [STEP StepValue]]

Enable automatic increment of the field. NextValue specifies the starting value and can be a positive or negative integer value in the range 2,147,483,647 to -2,147,483,647. The default value is 1. NextValue can be set using the Next Value spinner in the Fields tab in the Table Designer.

StepValue specifies the incremental value of the field and can be a positive, non-zero integer value in the range 1 to 255. The default value is 1. You can set the StepValue using the Step nudge in the Fields tab in the Table Designer.

Autoincrementing value cannot be NULL.

DEFAULT eExpression1

Specify the default value of the field. The data type of eExpression1 must be the same as the data type of the field.

A default value cannot be specified if autoincrementing is turned on for a field using the AUTOINC clause. Visual FoxPro generates an error message if you specify both the AUTOINC and DEFAULT clauses.

primary key | UNIQUE

primary key creates the primary index identifier. The index ID has the same name as the field. UNIQUE creates a candidate index ID with the same name as the field. For more information about candidate indexes, see Setting up a primary or candidate index.

COLLATE cCollateSequence

Specifies a comparison sequence other than the default MACHINE setting. The cCollateSequence parameter must be a valid Visual FoxPro comparison sequence. For details on setting the comparison sequence, see Optimizing International Applications and the set collate command.

REFERENCES TableName2 [TAG TagName1]

Specifies the parent table to establish a permanent relationship with.

Parameter TAG TagName1 specifies the parent table index identifier, and the relationship is based on this parent table index identifier. The index identifier can be up to 10 words long. If the TAG clause is omitted, 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.

NOCPTRANS

Prevents code page conversion of string or memo fields. If the table is converted to another code page, fields with NOCPTRANS specified are not converted. NOCPTRANS can only specify character fields and memo fields.

This creates the Character (binary) and Memo (binary) data types in the table designer.

NOVALIDATE

After selecting this option, Visual FoxPro can modify the structure of the table without being constrained by the integrity of the data in the table. By default, Visual FoxPro changes to the table structure will be subject to the integrity of the data in the table. Using the NOVALIDATE parameter will override the default.

ALTER [COLUMN] FieldName2

Specify the existing field name to be modified. You need to include multiple ALTER COLUMN clauses in a single ALTER TABLE command to modify more than one property of a field. For more information about the structure of the ALTER COLUMN clause, see the examples in this topic.

Set Default eExpression2

Specify a new default value for an existing field. The data type of eExpression2 must be the same as the field data type.

A default value cannot be specified if autoincrementing is turned on for a field using the AUTOINC clause. Visual FoxPro generates an error message if you specify both the AUTOINC and SET DEFAULT clauses.

SET CHECK lExpression2

Refers to the new validity rule for an existing field. The lExpression2 value must be a logical expression, or it can be a user-defined function or stored procedure.

ERROR cMessageText2

Specifies the error message displayed when an error occurs in the validity check. This message may only be displayed when data is changed in the Browse window or Edit window.

DROP DEFAULT

Delete the default value of an existing field.

DROP CHECK

Delete the validity rules of existing fields.

DROP [COLUMN] FieldName3

Deletes a field from the table. Deleting a field also deletes the field's default value and field validation rules.

After a field is deleted, index keywords or trigger expressions that reference this field will become invalid. In this case, deleting the field does not generate an error, but at run time, an invalid index key or trigger expression will cause an error.

SET CHECK lExpression3

Specify the validity rules of the table. lExpression3 must be a logical expression, or it can be a user-defined function or stored procedure.

ERROR cMessageText3

The error message displayed when an error occurs in the validity check of the specified table. This message may only be displayed when changing data values ??in the Browse or Edit window.

DROP CHECK

Delete the validity rules of the table.

ADD primary key eExpression3 [FOR lExpression4] TAG TagName2

Add a primary index to the table, eExpression specifies the primary index key expression.

You can use lExpression4 to specify a filter expression that only records that meet the criteria can be displayed and accessed. Primary index keys are created in the index file for records that match the filter criteria. You should avoid using the FOR clause to create a primary index; unique primary keys are enforced only on records that match the conditions specified in the FOR clause. Instead, use the INDEX command with the FOR clause to create a filtered index.

If lExpression4 expressions can be optimized, how does Rushmore query optimization optimize the ALTER TABLE ... FOR lExpression4 command. For better performance, use optimizable expressions in the FOR clause. For more information, see SET OPTIMIZE and Optimizing Data Access Speed ??with Rushmore Queries.

The TagName2 parameter specifies the name of the primary index identifier. Index identification names can contain up to 10 characters. If TAG TagName2 is omitted, and eExpression3 is a single field, the primary index ID has the same name as the field specified in eExpression3.

DROP primary key

Delete the primary index and its identifier. Because a table can have only one primary key, you do not have to specify the name of the key.

Deleting the primary index will also delete all permanent relationships based on this keyword.

ADD UNIQUE eExpression4 [TAG TagName3 [FOR lExpression5]]

Add candidate index to the table. eExpression4 specifies the candidate index keyword expression, and TagName3 specifies the candidate index identification name. Candidate identification names 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.

You can use the lExpression5 parameter to specify a filter expression that can only display and access conditions. Candidate index keys are created in the index file for records that match the filter criteria.

If the lExpression5 expression can be optimized, Rushmore can optimize the ALTER TABLE ... FOR lExpression5 command. For better performance, use optimizable expressions in the FOR clause. For more information, see SET OPTIMIZE and Optimizing Data Access Speed ??with Rushmore Queries.

DROP UNIQUE TAG TagName4

Delete the candidate index and its identifier. Because a table may have multiple candidate keys, candidate index identification names must be specified.

ADD foreign key [eExpression5] TAG TagName4 [FOR lExpression6]

Add a foreign key (non-primary key) index to the table. eExpression5 specifies the external index keyword expression, and TagName4 specifies the external index identification name. Index identification names can be up to 10 characters long.

You can use the lExpression6 parameter to specify a filter expression that only displays and accesses records that meet the criteria. External index keys are created in the index file for records that match the filter criteria.

If the lExpression6 expression can be optimized, Rushmore can optimize the ALTER TABLE ... FOR lExpression6 command. For better performance, use optimizable expressions in the FOR clause. For more information, see SET OPTIMIZE and Optimizing Data Access Speed ??with Rushmore Queries.

REFERENCES TableName2 [TAG TagName5]

Specifies the parent table on which the permanent relationship is created. Use TAG TagName5 to specify an existing index identifier, and establish a relationship between the table and the parent table based on this index identifier. Index identification names can be up to 10 characters long. If the parameter TAG TagName5 is omitted, the relationship is established using the primary index ID of the parent table.

DROP foreign key TAG TagName6 [SAVE]

Delete the foreign key with index ID TagName6. If the SAVE parameter is omitted, the index ID is removed from the structure index. If the SAVE parameter is included, the index identifier is not removed from the structural index.

RENAME COLUMN FieldName4 TO FieldName5

Allows changing the field names of fields in the table. FieldName4 specifies the field name to be changed, and FieldName5 specifies the new field name.

Warning Be careful when changing table field names - index expressions, field and table validation rules, commands, functions, and so on may still reference the original field names.

Remarks

The ALTER TABLE command can be used to modify the structure of a table that has not been added to the database. However, when modifying a free table, if you add a DEFAULT, FOREIGN KEY, PRIMARY REFERENCES, or SET clause, Visual FoxPro will issue 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.

After the table is rebuilt, field validation rules will be enforced on all fields that have changed type or width. If the type or width of any field in a table is modified, the table's validation rules are enforced.

If you modify the field validation rules or table validation rules of a table that already contains records, Visual FoxPro will check whether the new field or table validation rules are consistent with the existing data, and if any inconsistencies are found, Warnings are issued at all times.

You cannot specify a value or expression in the DEFAULT clause if autoincrementing is turned on for the field.

To remove autoincrementing, use ALTER TABLE - SQL to modify COLUMN to exclude the AUTOINC clause.

Field validation for Autoincrementing fields will remain in effect. You may want to use validation to return a new autoincrementing value.

When you want to modify a table that does not contain an autoincrementing field to include an autoincrementing field, either with the ALTER TABLE command or the table designer, autoincrementing starts on the next newly added record. Records already in the table are not updated with the autoincrementing value from the first record. You need to ensure that no conflicts occur in the results.

ALTER TABLE may not produce consistent results when using Visual FoxPro cursors created with the CREATE CURSOR command. In particular, you can create a Visual FoxPro cursor with features, such as long field names, that are typically only available on tables that are contained in database containers. ALTER TABLE saves a temporary copy of the cursor, so the rules that apply to free tables also apply, and any features that require database support are lost or modified in an unpredictable state. Therefore, you should generally avoid using ALTER TABLE on Visual FoxPro cursors unless you have tested it and know the results.

If NULL and NOT NULL are omitted, the current SET NULL command setting determines whether the field allows null values. However, if you omit NULL and NOT NULL but include a primary key or a UNIQUE clause, Visual FoxPro ignores the current SET NULL setting, and the field defaults to NOT NULL.

If the validation rules specified in the CHECK clause do not allow empty field values ??in the newly added fields, Visual FoxPro generates an error.

Null values ??and duplicate records are not allowed in fields using primary and candidate indexes. However, Visual FoxPro does not generate an error if you use ADD COLUMN to create a primary or candidate index that supports null values. Visual FoxPro generates an error if you try to enter a null or duplicate value into a field that uses a primary index or a candidate index.

Note that the candidate index created by the CREATE TABLE – SQL or ALTER TABLE – SQL command with the UNIQUE option (provided for compatibility with ANSI) 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 keys; candidate indexes do not allow duplicate index keys. For details about the UNIQUE option of the INDEX command, see the index command.

If you modify an existing field, and the primary index expression or candidate index expression consists of fields in the table, Visual FoxPro checks whether the field contains null values ??or duplicate records. If it is, Visual FoxPro generates the record and the table is not modified.

If the table is converted to another code page, fields with NOCPTRANS specified will not be converted.

Example

Example 1 Add the field fax to the table customer and allow the field to have null values.

Example 2 makes the cust_id field the primary key of the customer table.

Example 3 Add a validity rule to the quantity field in the orders table to make the value of the field quantity non-negative.

Example 4 establishes a one-to-many permanent relationship between customer and orders based on the keyword cust_id of the table customer and the candidate keyword cust_id in the table orders.

Example 5 Delete the validity rule of the quantity field of the table orders.

Example 6 deletes the permanent relationship between the customer table and the orders table, but maintains the cust_id index identifier in the orders table.

Example 7 adds a field called Fax2 to the Customer table and prevents the field from containing null values. Show the new table structure. Two ALTER COLUMN clauses are used to allow a field to have a null value and to set the field's default value to a null value. Note that multiple ALTER COLUMN clauses in a single ALTER TABLE command are required to change more than one attribute of a field. Then delete the new fields from the table to restore the table to its original state.

* Example 1

SET PATH TO (HOME(2) + 'Data\') && Sets path to table.

ALTER TABLE Customer ADD COLUMN Fax c(20) 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

* Example 7

CLEAR

ALTER TABLE Customer ADD COLUMN Fax2 c(20) NOT NULL

DISPLAY STRUCTURE

ALTER TABLE Customer;

ALTER COLUMN Fax2 NULL;

ALTER COLUMN Fax2 SET DEFAULT .NULL.

ALTER TABLE Customer DROP COLUMN Fax2

The following example uses NOCPTRANS to Prevent conversion to a different code page. The example creates a temporary table named "mycursor" containing two character fields and two memo fields. The second character field "char2" and the second memo field "memo2" contain NOCPTRANS to prevent conversion.

CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,;

memo1 M, memo2 M NOCPTRANS)