ALTER TABLE SQL Command

Use this SQL command in order to modify the structure of a DBF file. There are the possible syntaxes:

ALTER TABLE Table_Name ADD | ALTER [COLUMN] FieldName1
[FieldType [( nFieldWidth [, nPrecision])]]
[I AUTOINC [NEXTVALUE NextValue [STEP StepValue]]] [NULL | NOT NULL]
ALTER TABLE Table_Name [DROP [COLUMN] FieldName1]
[RENAME COLUMN FieldName1 TO FieldName2]

Parameters

ALTER TABLE Table_Name

Modifies the structure of the specified table. The Table_Name parameter should contain full (or relative) DBF file name to be modified.

ADD | ALTER [COLUMN] FieldName1

Specifies the name of the field to add or alter. The COLUMN word is not obligatory, but can be used.

Maximal field count a DBF table can contain is 255 fields. If one or more fields allow null values, the limit decreases to 254 fields.

[FieldType [( nFieldWidth[, nPrecision])]]

Specifies the field type, field width, and field precision (number of decimal places) for the new or altered field.

The FieldType parameter is a single letter (or long name) indicating the field type. You can specify nFieldWidth, nPrecision, or both for some of field types. The following table lists the values for FieldType and whether you can specify nFieldWidth and nPrecision:

Field Type Field Width Precision Data type
W, Blob – – Blob
C, Char, Character N – Character field of width N
Y, Currency – – Currency
D, Date – – Date
T, DateTime – – DateTime
B, Double – D Double
G, General – – General
I, Int, Integer – – Integer
L, Logical – – Logical
M, Memo – – Memo
N, Num, Numeric N D Numeric field of width N with D decimal places
F, Float N D Floating Numeric field of width N with D decimal places

The nFieldWidth and nPrecision parameters are ignored for the Blob, Currency, Date, DateTime, General, Integer, Logical, and Memo field types. If nPrecision is not included for the Numeric or Float types, the nPrecision parameter defaults to zero (no decimal places).

AUTOINC [NEXTVALUE NextValue [STEP StepValue]]

Enables autoincrementing for the Integer field. NextValue specifies the start value and can be a positive or a negative value ranging from Integer range: -2,147,483,647 to 2,147,483,647. The default value is 1.
StepValue specifies the increment value for the field and can be a positive, nonzero integer value ranging from 1 to 255. The default value is 1.

Autoincrementing values cannot be NULL!

When you change a table that does not contain an autoincrementing field to include one, either by using ALTER TABLE SQL clause or the File Structure dialog box, autoincrementing begins with the next record will be added. Previous rows in the table are not updated with autoincrementing values starting with the first record.

NULL | NOT NULL

Specifies whether null values are allowed in the field: NULL permits null values, NOT NULL does not allow null values. By default, NULL values are allowed (except AUTOINC fields)

If the DBF file you are modifying contains records, and you want to forbid null values in new or altered field using NOT NULL clause, the error "Field FIELDNAME does not accept null values" will occurs. Since the default value for empty cell is null, DBF Commander tries to fill out new field with default values. But the field cannot contain nulls, and so we have the error.
If the DBF file you alter contains no data, this issue won't occurs.
DROP [COLUMN] FieldName1

Specifies a field name to be deleted from the DBF file.

RENAME COLUMN FieldName1 TO FieldName2

Renames a field in the DBF table. The FieldName1 and FieldName2 parameters are the old and new field's names, respectively.

Please be careful when renaming table fields because previous SQL queries and commands might refer to the original field names.

 

ALTER TABLE Example

Add a field called NewField to an Orders table and forbid the field to have null values (file orders.dbf contains no records):

ALTER TABLE "D:\Data\orders.dbf" ADD newfield C(25) NOT NULL

Change the field type to Integer Autoinc:

ALTER TABLE "D:\Data\orders.dbf" ALTER newfield I AUTOINC NEXTVALUE 100 STEP 1

Then, rename the column:

ALTER TABLE orders RENAME COLUMN newfield TO lastfield

And delete the field:

ALTER TABLE orders DROP COLUMN lastfield