Primary keys

Primary keys in SQLite

SQLite tables may declare a column to be a PRIMARY KEY. When such a column is an integer type, it is known as an INTEGER PRIMARY KEY and acts as an alias to the internal SQLite rowid column, a 64-bit integer uniquely identifying the row in the table. Non-integer primary keys are similar to UNIQUE constraints, except that there can be only one primary key per table, while there can be any number of unique constraints. Non-integer primary keys are documented in detail here, and the special behavior of integer primary keys is documented here.

Primary keys in older versions of SquiDB

Prior to version 3.1.0, SquiDB would generate a property named ID in all table models, with an underlying column name of _id. This column was declared as an INTEGER PRIMARY KEY, and as such acted as an alias for the SQLite rowid as described above. This ID property was also used for some internal SquiDB bookkeeping in concert with the TableModel methods getId() and setId(). Also prior to version 3.1.0, users could use the @PrimaryKey annotation to name this ID property and/or its underlying column something else, although this annotation was restricted to fields of type long only.

Improvements in SquiDB 3.1.0

As of version 3.1.0, many of these restrictions have been removed. The eventual goal is to remove this default ID property and its associated getId() and setId() methods entirely and use only the internal/reserved rowid property for SquiDB bookkeeping. This could not be done all at once without breaking backwards compatibility, but 3.1.0 takes several major steps towards this end. The important changes are as follows:

• The @PrimaryKey annotation can now annotate any field, not just long fields.
  • If the annotated field is an integer type, that makes the generated property an INTEGER PRIMARY KEY. This field will replace the default ID field and be used for bookkeeping. This is basically how @PrimaryKey worked prior to 3.1.0 as well.
  • If the annotated field is a non-integer type, that means the user has not declared an INTEGER PRIMARY KEY. In this case, SquiDB will generate a ROWID property explicitly referencing the rowid column, and this column will be used for bookkeeping purposes. It will be part of the PROPERTIES array for the model, but not explicitly created in the table (SQLite creates the column implicitly for all tables).
• The @TableModelSpec annotation has a new option, boolean noRowIdAlias(). If this flag is set, SquiDB will not generate the ID property as an INTEGER PRIMARY KEY alias to the rowid, but instead generate a ROWID property explicitly referencing the rowid column. This property will be used for bookkeeping instead of ID. Users who wish not to declare an explicit INTEGER PRIMARY KEY should use this option.
  • This will be the default behavior in the next major version of SquiDB, at which time this flag will become unnecessary and be removed
• Users who desire a multi-column primary key can declare the key using the tableConstraints() field in @TableModelSpec. Note that this also requires enabling the noRowIdAlias() flag, as SQLite only allows a single PRIMARY KEY declaration when creating the table.
• If none of the above apply, SquiDB will continue to generate the ID property as an INTEGER PRIMARY KEY alias to the rowid. However, it will also log a warning since users will need to explicitly declare these rowid aliases in a future major version of SquiDB. If they update their model specs to explicitly declare these aliases now, this warning will go away and things will work as they always have.
• In order to maintain backwards compatibility, user-defined properties cannot be named ID, as their getter/setter would conflict with the deprecated TableModel getId() and setId().
  • This restriction does not apply if the field is an INTEGER PRIMARY KEY, as it then replaces the default ID property.
  • This restriction will be removed in a future version of SquiDB.
• User-defined columns named rowid are not permitted.
• SquiDB does not support WITHOUT ROWID tables. (This has always been the case, but is worth mentioning explicitly).

Future-proofing your code/upgrading to SquiDB 3.1.0

Existing users who upgrade to SquiDB 3.1.0 will notice several new warnings logged by the code generator. For all table model specs that do not explicitly declare a @PrimaryKey column, SquiDB will continue to generate the ID property with column name _id by default. In a future major release though, SquiDB will stop generating this column and generate an explicit reference to rowid instead, meaning the ID property will disappear. All users should update any calls to the deprecated getId() and setId() to use getRowId() and setRowId() instead. For any existing tables, users can do one of the following two things to remove the warning and future-proof their code:

• Option 1: Explicitly declare the default ID property in your table model specs. The spec field would look like this:

@TableModelSpec(...)
class MyModel {
    // This replaces the legacy ID property that was generated by default 
    // with one that is semantically identical
    @PrimaryKey
    @ColumnSpec(name = "_id")
    long id;
}

This will remove the code generator warning by simply making the default INTEGER PRIMARY KEY column explicit.

• Option 2: Enable the noRowIdAlias flag in @TableModelSpec and update any code that references ID or its accessors to refer to the ROWID column instead. Users who take this option may want to run a database migration to drop the default _id column in case they want to add a different primary key to the table in the future. Dropping columns in SQLite is a pain, but this option will be closer to how SquiDB will behave for new tables in a future major version.