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 ]

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

See Also

AfterAddRelation, AfterDropRelation, AfterModifyTable, BeforeAddRelation, BeforeDropRelation, BeforeModifyTable, Create Cursor, Create Table, Index, Modify Structure, Set Safety