Alter Table

We waited for this command for years. Finally, starting in VFP 3, we could change the structure of a table without resorting to all kinds of tricks. ALTER TABLE lets you add and remove fields, change a field, or change defaults and rules. ALTER TABLE works not only on tables, but also on cursors built with CREATE CURSOR, though some clauses aren't relevant for cursors.

Usage

ALTER TABLE TableName
      ADD | ALTER [ COLUMN ] FieldName
            FieldType [ ( nFieldWidth [ , nDecimals ] ) ]
            [ NULL | NOT NULL ]
            [ CHECK lFieldRule [ ERROR cFieldRuleMessage ] ]
            [ DEFAULT uDefaultExpression ]
            [ PRIMARY KEY | UNIQUE ]
            [ REFERENCES ReferencedTable 
                     [ TAG ReferencedTag ] ]
            [ NOCPTRANS ]
      [ NOVALIDATE ]
ALTER TABLE TableName
      ALTER [ COLUMN ] FieldName
            [ NULL | NOT NULL ]
            [ SET DEFAULT uDefaultExpression]
            [ SET CHECK lFieldRule [ ERROR cFieldRuleMessage ] ]
            [ DROP DEFAULT ]
            [ DROP CHECK ]
      [ NOVALIDATE ]
ALTER TABLE TableName
      [ DROP [ COLUMN ] FieldName ]
      [ SET CHECK lTableRule [ ERROR cTableRuleMessage ] ]
      [ DROP CHECK ]
      [ ADD PRIMARY KEY uPrimaryKeyExpression 
            [ [ FOR lPrimaryKeyFilter ] TAG PrimaryKeyTag ] ]
      [ DROP PRIMARY KEY ]
      [ ADD UNIQUE uUniqueKeyExpression
            [ FOR lUniqueKeyFilter ] [ TAG UniqueKeyTag1 ] ]
      [ DROP UNIQUE TAG UniqueKeyTag2 ]
      [ ADD FOREIGN KEY [ uForeignKeyExpression ] 
            [ FOR lForeignKeyFilter ] TAG ForeignKeyTag1 
        REFERENCES ReferencedTable [ TAG ReferencedTag ] ]
      [ DROP FOREIGN KEY TAG ForeignKeyTag2 [ SAVE ] ]
      [ RENAME COLUMN OldFieldName TO NewFieldName ]
      [ NOVALIDATE ]

Parameter	Value	Meaning
FieldName	Name	The name of the field in the table to be added, changed or deleted. Long names can be used for tables in a database.
FieldType	Single character	The type to use for the field. Valid types are: C – Character D – Date T – DateTime N – Numeric F – Float (same as numeric) I – Integer B – Double Y – Currency L – Logical M – Memo G – General P – Picture
nFieldWidth	Numeric	The width of the field. For many types, the width is fixed and nFieldWidth should be omitted. For Double fields, nFieldWidth must be omitted.
nDecimals	Numeric	The number of decimal places for the field. Relevant only for some of the numeric types. For Double fields, nDecimals may be included, even though nFieldWidth is omitted. For example, ALTER TABLE MyTable ADD COLUMN MyNewField B(3) creates a Double field with three decimal places.
lFieldRule	Logical	An expression that provides the field-level rule. Can call a function.
cFieldRuleMessage	Character	The error message to display when the field rule is violated.
uDefaultExpression	Same type as specified by FieldType	An expression that provides a default value for the field.
ReferencedTable	Name	The name of the table for which the field or expression is a foreign key. A regular index tag is created for this field or expression.
ReferencedTag	Name	The name of the tag in ReferencedTable to which the field or expression refers.
ReferencedTag	Omitted	The field or expression refers to the primary key in ReferencedTable.
lTableRule	Logical	An expression that provides the table-level rule. Functions can be called. In VFP 3, table-level rules may not change the data in the table.
cTableRuleMessage	Character	The error message to display when the table-level rule is violated.
uPrimaryKeyExpression	Any type but Memo, General, or Picture	An expression that forms the primary key for this table.
PrimaryKeyTag	Name	The name to assign the tag created for the primary key.
lPrimaryKeyFilter	Logical	An expression that filters the records in the primary key index. Only records for which the expression is true are included in the index. Don't do this—see below for the reason.
uUniqueKeyExpression	Any type but Memo, General, or Picture	An expression that forms a candidate key for this table.
UniqueKeyTag1	Name	The name to assign the candidate key tag created.
lUniqueKeyFilter	Logical	An expression that filters the records in the candidate key index. Only records for which the expression is true are included in the index. Don't do this either, although we don't feel quite as strongly about this one.
UniqueKeyTag2	Name	The name of a candidate key to be deleted.
uForeignKeyExpression	Any type but Memo, General, or Picture	An expression that is a foreign key into another table. A regular index tag is created for this expression.
ForeignKeyTag1	Name	The name of the tag to be created for uForeignKeyExpression.
lForeignKeyFilter	Logical	An expression that filters the records in the foreign key index. Only records for which the expression is true are included in the index. Need we say it again—don't do this.
ForeignKeyTag2	Name	The name of the tag that is part of a persistent relation to be removed. If SAVE is included, the tag is retained—only the relation is removed. If SAVE is omitted, the tag is also deleted.
OldFieldName	Name	The name of a field to be renamed.
NewFieldName	Name	The new name of the field.

There are three fundamental ways to use ALTER TABLE: You can add a field ("column" as this command calls it), change a field, or change the table as a whole. Adding a field offers all the same bells and whistles as in CREATE TABLE. Changing a field can mean changing either its basic attributes, like type and size, or changing its database attributes, like default and rule. There are all kinds of ways to change the table itself, including changing its rule, removing fields, and changing indexes. The three main ways to change a table, however, don't exactly map to the three forms of the command. The first form lets you both add and change fields. The second form is for field changes that don't involve the basic attributes, and the third form is for table changes. We're not sure why three forms are necessary, but we suspect it boils down to conforming to ANSI SQL. Fair enough. Of course, the FOR clauses added in VFP 6 surely are a violation of the ANSI standard for SQL.ALTER TABLE requires exclusive use of the table. In VFP 3, if the table is in a database, the database has to be open exclusively as well. In VFP 5 or later, the database must not be opened NOUPDATE; SHARED or EXCLUSIVE will work.There are many ways in which changes can go wrong. For example, when you add a rule, all existing records are checked to be sure they meet the new criteria. If they don't, the changes fail. Because one problem can cause the entire command to fail, you don't want to issue massive ALTER TABLEs that affect lots of things at once. Instead, take it one step at time, issuing a series of ALTER TABLE commands. (In fact, only some clauses can be combined or repeated.)NOVALIDATE tells FoxPro to change the structure of the table without checking that all existing records meet all the new criteria. It's dangerous. While it can be helpful in some situations, it also can leave you with invalid data. In that case, the rules don't get checked until you actually change a field affected by a rule—passing through the field isn't sufficient. You may want to follow an ALTER TABLE NOVALIDATE with a loop that REPLACEs things and traps the errors so you can deal with them.When you use ALTER COLUMN to specify a new type or size for an existing field, be sure to specify all the information for that field. ALTER TABLE treats this operation as if you'd deleted the field definition and then added it back (your data is unharmed). So, the settings for NULL and NOCPTRANS are lost, as are the default value and rule. You may want to use AFIELDS() to collect the information first, so you can reapply it.When you use the second form of the command, though, giving only the name of the field, FoxPro understands that you're making changes to the field and changes only those items you specify.If you want to remove a field that's part of a primary or candidate key, you have to explicitly DROP that tag as well as the field. You can do this in one ALTER TABLE command, but failing to DROP the tag generates an error.RENAME COLUMN, on the other hand, won't let you shoot yourself in the foot too badly (unless you use NOVALIDATE, of course). If you try to rename a field that's used in a rule or a key expression, you get an error. Of course, you might have referenced the field elsewhere, so you still have to be careful.

You'll get weird errors and unexpected behavior if you try to remove both the default and rule for a field in the same statement; the error and behavior depend on the order in which you use the DROP clauses. DROP DEFAULT DROP CHECK results in either a "no rule to drop" error if there's no table-level rule, or removes the table-level rule if there was one. (Clearly, VFP is confusing the second and third forms of the command in this case.) DROP CHECK DROP DEFAULT results in a "variable 'default' is not found" error. Using these clauses in separate commands works fine.

DROP FOREIGN KEY is confusing because it can do two different things. Its main role is to remove persistent relations—it deletes the relation of which the named tag forms the many side. If you don't specify SAVE, the tag itself is also deleted.One Xbase thing that the SQL table handling commands haven't allowed is filtering an index tag; that is, specifying that only certain records should appear when a certain tag is in use. Starting with VFP 6, you can add filters to keys with ALTER TABLE. On the whole, we suggest you stay away from filtered indexes. Rushmore doesn't use them and they're likely to cause confusion. The one situation where we can see the point is if you're recycling deleted records—in that case, you may want to filter your primary key on DELETED() so you don't run into uniqueness problems. If you do so, be sure to create a regular, unfiltered index tag for that field, too, so Rushmore can help you out.

The order of the FOR and TAG clauses is important: You'll get a "syntax error" if you try to use TAG before FOR.

In VFP 7, if the table being modified belongs to a database and that database has database events turned on, the BeforeModifyTable and AfterModifyTable events fire. In addition, if a foreign key is created (using either the REFERENCES clause in the first form of the command or the ADD FOREIGN KEY clause in the third form), the BeforeAddRelation and AfterAddRelation events fire. If a foreign key is removed using the DROP FOREIGN KEY clause, the BeforeDropRelation and AfterDropRelation events fire.

Example

OPEN DATABASE Testing
CREATE TABLE Test1 (cId C(3), cName c(20) UNIQUE)
CREATE TABLE Test2 (cTest1Id C(3), dDate D) 
ALTER TABLE Test1 ADD COLUMN nTotal N(4)
ALTER TABLE Test1 ALTER COLUMN cId C(3) PRIMARY KEY ;
      DEFAULT GetId("Test1")
ALTER TABLE Test1 ALTER COLUMN cName SET CHECK NOT EMPTY(cName)
ALTER TABLE Test1 DROP UNIQUE TAG cName
ALTER TABLE Test2 ADD FOREIGN KEY cTest1Id TAG cTest1Id ;
      REFERENCES Test1