CREATE TABLE

This is a case of practically a whole language in a single command. This command, which lets you define tables programmatically, has been tremendously enhanced in Visual FoxPro. Since it was introduced in FoxPro 2.0, CREATE TABLE has made life a lot simpler for developers. Before that, creating a table meant you had to have something to start with—usually another table with the same or similar structure to the one you wanted to create. Then you could apply COPY STRUCTURE or the pair COPY STRUCTURE EXTENDED and CREATE TABLE FROM. CREATE TABLE freed us from that nonsense.

In Visual FoxPro, CREATE TABLE has whole new aspects. Not only can it create the table itself, but it can also create index tags and persistent relations. In addition, it lets you set both field-level and row-level rules and establish default values.

Usage

CREATE TABLE | DBF TableName [ NAME LongTableName ] [ FREE ]
         ( FieldName1 FieldType1
            [ ( nFieldWidth1 [ , nDecimals1 ] ) ]
            [ NULL | NOT NULL ]
            [ CHECK lFieldRule1 [ ERROR cFieldRuleMessage1 ] ]
            [ DEFAULT eDefaultExpression1 ]
            [ PRIMARY KEY | UNIQUE ]
            [ REFERENCES ReferencedTable1
                   [ TAG ReferencedTag1 ] ]
            [ NOCPTRANS ]
          [, FieldName2 ... ]
          [, PRIMARY KEY ePrimaryKeyExpression
                   TAG PrimaryKeyTag ]
          [, UNIQUE eUniqueKeyExpression1 TAG UniqueKeyTag1
            [, UNIQUE eUniqueKeyExpression2 ... ] ]
          [, FOREIGN KEY eForeignKeyExpression1
                  TAG ForeignKeyTag1 [ NODUP ]
             REFERENCES ReferencedTable2
                  [ TAG ReferencedTag2 ]
              [, FOREIGN KEY eForeignKeyExpression2 ... ] ]
          [, CHECK lTableRule [ ERROR cTableRuleMessage] ]
           )
          | FROM ARRAY ArrayName

There’s so much going on in this one command that you could (and we did!) spend days testing all the combinations. Let’s try to break it down into logical components.

The first thing is to note that there’s information at two levels: table and field. The initial part of the command until the opening parenthesis applies to the table, as does everything following the field list. (In the syntax diagram, that’s everything from the second PRIMARY KEY to the end.)

Many of the clauses appear to be repeated. That’s because you can specify a number of things at both levels. Within the field list, for example, CHECK indicates a field-level rule. After the field list, it indicates a table-level rule.

In addition, some items can appear more than once. Of course, the whole field listing is available for each field. In addition, at the table level, you can specify multiple candidate keys (with the UNIQUE clause) and multiple foreign keys.

Types of Tables

You can create two different types of tables: free tables and tables that belong to a database. Many clauses apply only when you’re adding a table to a database. They are NAME, CHECK (and ERROR), DEFAULT, PRIMARY KEY, FOREIGN KEY, and REFERENCES (and TAG), at all levels where they appear. The attributes created by these clauses apply only to tables in a database. If the table is created in a database that has Database Events turned on, the BeforeCreateTable and AfterCreateTable events fire.

The FREE keyword is needed only if there’s a database set and you don’t want the new table to belong to it. With no database set, free tables are created automatically.

In VFP 3, to add tables to a database, the database had to be open exclusively. In VFP 5 and VFP 6, you can add tables to a shared database. Regardless, the new table is opened exclusively in an available work area.

Field Information

For each field, we start out with the same information we’ve always had—a field name, type and size. But there’s so much more here now. CREATE TABLE rightfully rejects certain VFP reserved words as field names—things like PRIMARY and FOREIGN that are just too confusing for it to deal with. Unfortunately, the Table Designer isn’t as discriminating and lets you use these keywords as field names. If you’re stuck with such a table and need to re-create it with code, the solution is to surround the offending field names with quotes. Of course, it would be even better if the Table Designer wouldn’t let you shoot yourself in the foot this way.

Fields can accept or reject nulls (poor nulls, being rejected all the time). Generally, we recommend you allow nulls only where they’re meaningful—dealing with them is a pain, so don’t have more than you need. SET NULL determines whether any field accepts null, unless you specify NULL or NOT NULL. Like every good rule, there’s one exception: If you omit both NULL and NOT NULL, but include PRIMARY KEY or UNIQUE, the field doesn’t accept nulls.

NOCPTRANS determines whether codepage translation affects the field. Some kinds of data (for example, binary keys) are damaged when translated to another codepage—you can indicate which fields are badly affected. When the table is opened under another codepage, those fields are unchanged.

Indexes

Several kinds of indexes can be created right along with the table. As expected, the PRIMARY KEY clause establishes a primary key for the table—one to a customer, please.

The UNIQUE clause that appears at both the field and table levels has nothing to do with Xbase’s traditional (and not really) unique indexes—the ones you create by SETting UNIQUE ON or adding the UNIQUE keyword to INDEX. UNIQUE in CREATE TABLE produces what’s called a “candidate key”—like a primary key, no duplicates are allowed. The main difference between a primary key and a candidate key is that we’ve indicated that the primary key is primary. The other difference is that free tables can have candidate keys, but not primary keys.

You can't specify primary or candidate keys at the field level for fields using long names. When you try, you get the error message "Tag name is too long." That actually makes sense because the field name is used as the tag name, and tag names are still limited to 10 characters. In fact, maybe that limit is the real bug here.

The REFERENCES clause at the field level and the FOREIGN KEY clause at the table level let you create regular indexes that are related to other tables in the database. (Include NODUP to make it a candidate rather than a regular index.) You specify an expression in this table (at the field level, it’s the field itself) that relates to an existing tag in another table. The REFERENCES portion mentions the other table and the tag you’re relating to there.

You can, of course, create additional indexes with the INDEX command.

Rules

Tables in a database can have rules at both the field and table levels—that’s the CHECK clause. Rules are your opportunity to make sure newly entered data is good enough for the field or table. In VFP 3, rules were not permitted to change the record. In versions starting with VFP 5, you can change the values of fields of the current record in rule code. Be careful—it’s your responsibility to make sure that the changes don’t result in an infinite sequence of calls to the rule code.

Hip, Hip, Array!

There’s an alternative to listing out all the fields with all their information. Put the data into an array (with the same structure as the ones created by AFIELDS()) and just base the table on the array. This is a great choice when you want a table that’s similar, but not identical, to an existing table. Use AFIELDS() on the first table, modify the array, then CREATE TABLE from it. One caveat: Remember to change the long table name in the array (column 12 in the first row) or you may get an error message telling you that the name has already been used (for example, if you use AFIELDS() on one table in a database and use the array to create another table in the same database).

Index information doesn’t come along this way. When you CREATE TABLE from an array, all you get is the field structure (including rules, defaults, and so forth) and the table-level rules and triggers.

Triggers

If you want to create triggers at the same time you create the table, you must create the table using an array. Otherwise, you can add triggers later with the CREATE TRIGGER command.

Example

* Create a near duplicate of an existing table
USE Original
nFldCount=AFIELDS(aStruc)
aStruc[1,12]="NewOne"
DIMENSION aStruc(nFldCount+1,ALEN(aStruc,2))
aStruc[nFldCount+1,1]="cMyNewField"  && Name
aStruc[nFldCount+1,2]="C"            && Type
aStruc[nFldCount+1,3]=5              && Size
aStruc[nFldCount+1,4]=0              && Decimals
aStruc[nFldCount+1,5]=.F.            && Nulls
aStruc[nFldCount+1,6]=.F.            && No CP
aStruc[nFldCount+1,7]="JUNK"         && Default
aStruc[nFldCount+1,8]=""             && Rule
aStruc[nFldCount+1,9]=""             && Error
CREATE TABLE NewOne FROM ARRAY aStruc

* Create a database from scratch
CREATE DATABASE Clients
CREATE TABLE Client ;
   (cClientId C(6) DEFAULT GetId("Client") ;
                   PRIMARY KEY NOCPTRANS, ;
    cLastName C(20) CHECK NOT EMPTY(cLastName) ;
                    ERROR "Must provide last name", ;
    cFirstName C(15), ;
    mStreetAddress M, ;
    cCity C(20), ;
    cState C(2) CHECK EMPTY(cState) OR ;
                      LookUpState(cState) ;
                ERROR "Unknown State", ;
    cZip C(9), ;
    bCredit B(2), ;
    CHECK NOT (EMPTY(cState) AND NOT EMPTY(cCity)) ;
   )
CREATE TABLE Meetings ;
   (cMeetingId C(6) DEFAULT GetId("Meetings") ;
                    PRIMARY KEY NOCPTRANS, ;
    dMeetingDate D, ;
    cClientId C(6) REFERENCES Client, ;
    nMeetingLength N(3) ;
   )

In the first example, we add a field to the list from an existing table and create a new table. In the second example, we create a database with two tables. Each has a primary key (as all good tables should) and each has a default value that calls a routine that generates a new key. There’s a persistent relation between the cClientId fields of the two tables. Several fields have rules. The rule for cState lets you leave it empty, but if it’s not empty, you had better be able to find it by looking it up. Clients has a table-level rule, too, which says that if you fill in the city, you have to fill in the state.

See Also

AFields(), Alter Table, AfterCreateTable, BeforeCreateTable, Copy Structure, Create Cursor, Create From, Create Trigger, Index, Set NoCPTrans, Set Null