How to Enforce Uniqueness in Visual FoxPro Memo Fields

MORE INFORMATION

Example Scenario

When you have a long character field that happens to be rarely filled, it might be better to store the data in a memo field instead of a character field. The memo field takes up only four bytes in the table, and the large character field can now be removed from the structure.

You can use the following step-by-step procedure to create a unique index tag on just a portion of the data in the memo field. The benefits of this process are:

• Guaranteed uniqueness in large data sets.
• The tag size remains the same as it was.
• The size of the .dbf file shrinks dramatically.

Step-by-Step Procedure

1. Create a new database (.dbc file):
   1. On the File menu, click New.
   2. Select Database, and then click the New File button.
   3. Place the new database (.dbc file) in the directory of your choice.
2. Add a table to the database:
   1. Right-click the Database Designer, and select New Table.
   2. Click the New Table button.
   3. Create the table in the directory of your choice.
   4. In the Table Designer, add a memo field.
3. Add a Unique Index Tag to the Table:
   1. Click the Index tab.
   2. Under Name, type the name of your memo field.
   3. Under Type, select Primary or Candidate (Unique won't work).
   4. For the expression, enter the following where MaxLen is any value up to the maximum of 240:

            PADR(MemoField, MaxLen) 

4. Add several records to the table with exactly the same content in the memo field to verify a proper setup.