About Primary Keys

About primary keys and value generators

Most database tables have a column that is a "primary key" column. The values in this column uniquely identify the row in the table, most commonly by means of an integer value. When rows are inserted into the table these values are automatically generated. The values themselves should be thought of as as being meaningless and are just used to identify the row in the database.

To be used in IJC a table must have such a column that can be used to uniquely identify each row in the table. Typically this is the primary key column and IJC will use this as the ID field for the Entity that uses the table. Prior to IJC 5.3 IJC could only use tables with integer values for this column, but since 5.3 it now has some support for text based columns.

About value generators

IJC uses a "value generator" that defines how the values for the ID field (primary key column) are generated. Different table types need different types of value generator, and the type is dependent on the type of database. The value generator is defined when the entity is created or promoted and from that point onwards the way the IDs are generated is transparent to the IJC user.

IJC currently supports these types of value generator. Other types may be added in future.

Autoincrement

  • Values are generated automatically by the database using the native mechanism supported by the database. e.g. for MySQL databases this is using the auto_increment feature and for Derby databases it uses the identity feature. In both cases integer values are generated automatically, usually starting from 1 and increasing by 1 for each row inserted. Oracle does not support such a mechanism.

Sequence

  • Values are generated from a sequence and inserted into the row along with the other values. This is only supported on databases that support sequences e.g. Oracle.

JChem standard

  • Values are generated internally by JChem. This must be used for all JChemBase tables. Internally JChem uses whatever mechanism is appropriate for the type of database (e.g. auto_increment for MySQL) but this is transparant to the user.

GUID

  • 32 character text values are generated and used for the ID field values. These values are guaranteed to be unique. The Column used by the ID field must support text values of at least 32 characters.

None

  • No mechanism is defined for generating IDs, and so new rows cannot be inserted into the table. Rows can be updated, deleted and queried, but not inserted.

Limitations of IJC's support for primary key columns and ID generation

  • Currently IJC only supports tables that have a single column as their primary key. Composite primary keys are not yet supported.

  • Currently IJC can only create tables that use integer based primary keys. Tables with text based primary keys must be created externally and then "promoted" into IJC.

  • Integer primary keys only support a limited number of mechanisms for ID generation:

    • Autoincrement (MySQL)

    • Identity (Derby)

    • Sequence (Oracle) Use of these is transparent to the IJC user.

  • Text primary keys only support a limited number of mechanisms for ID generation:

    • 32 character GUID (all databases) Use is transparent to the IJC user.
      If you have a table which uses an alternative mechanism for generating values for the primary key column (e.g. trigger based approaches) then you cannot currently use this mechanism in IJC (we plan to add this in future). However you can specify that you want to use no value generator when you promote the table and this will allow you to use the table but not insert any new rows into it. The table must still have a column that can be used as the ID field (e.g. a primary key column) as described above.

When promoting a database view there is no primary key column that can be directly determined from the database, but IJC still needs a column that can be used for the ID field. As the column cannot be determined automatically the user must specify a suitable column. Again this column is subject to the restrictions described above, and of course it must contain values that uniquely identify each row in the view.