PRB: ALTER COLUMN Clause Applies Only to First SET After It

ID: Q135331

The information in this article applies to:

• Microsoft Visual FoxPro for Windows, version 3.0

SYMPTOMS

The command below sets the default value for the maxordamt field to 500 and the row level validation rule to maxordamt >10. It does not set the field validation rule to maxordamt >10 as you would expect:

   ALTER TABLE customer ALTER COLUMN maxordamt SET DEFAULT 500 ;
      SET CHECK maxordamt >10

CAUSE

The ALTER TABLE command uses SET CHECK to set field and row level validation. Each SET CHECK that applies to a field must be preceded by ALTER COLUMN to distinguish it from row level validation.

RESOLUTION

For each property of a field you wish to SET, issue a separate ALTER COLUMN. A separate ALTER COLUMN must also be issued for each DROP.

To set the default and the field level validation for the maxordamt, use this command:

   ALTER TABLE customer ALTER COLUMN maxordamt SET DEFAULT 500 ;
      ALTER COLUMN SET CHECK maxordamt >10

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. To see the difference between the two commands, open and modify the

   Testdata database in the Samples\Data directory.

   earlier in this article).

   Database Designer window, and select Modify.

   rule has been set.

6. Delete both rules and answer Yes to modify structure.

7. Issue the second command in the Command window (see the Resolution

   section earlier in this article).

   Database Designer window, and select Modify.

   rule under field properties now shows maxordamt>0. There should not be a
   validation rule showing under table properties.