Primary and Foreign Keys

Parent Previous Next

  1. Primary keys are either "natural" or "surrogate" -- you should know the difference, and when to use either.

    Notes: This is an important area and additional reading is required if you are not sure. Briefly, an example natural key is Employee_No where the value is assigned by your HR Department. One example of a surrogate key is the GUID, a long string of characters guaranteed to be unique. Another more common surrogate key is an auto-increment integer value (1000, 1001, 1002, etc.)

  2. Primary keys should include the table name followed by "_id" (or "_no", "_code", etc.) e.g., "product_id". In most cases, all other fields in the table will exclude the table name.

    Reason: Including the table name in the Primary Key will allow Alpha to automatically suggest matching key values when adding Linked Grids or SQL Joins.

  3. Use the field name suffix "_id" for surrogate fields, and "_no" or "_code (etc.) for natural keys. For instance, "item_id" if the primary key is an auto-increment (surrogate), but "item_no" if that Item Number is a natural key, a value provided perhaps by the Manufacturing Department.
  4. Foreign Keys should be named the same as the Primary Key in the parent table. For instance, in a table named product_detail, name the foreign key to the product table would be "product_id".
  5. DBF-only: It is common for DBF tables to have been assigned alphanumeric auto-increment values (AA100, etc.) You are encouraged to use numeric field type with auto-increment if you plan ever to convert your tables to SQL.
  6. If users need to see the primary key (e.g., "Your order number is 100023") it is good practice to start your integer auto-increment value with an "attractive" number like 10000 rather than 1. But technically this is not required.