To retrieve a single specific record from a table, the record must be uniquely identified. The solidDB® server uses primary keys to uniquely identify each record in each table. A primary key is a column or combination of columns that contains a unique value or combination of values. Each table has a primary key — either explicit or implicit.
The solidDB® server creates a primary key index automatically based on the field or fields of the primary key. A primary key index, like any index, speeds up access to data in the table. Unlike other indexes, however, a primary key index also controls the order in which records are stored in the database. This is called clustering. Records are stored in ascending order based on the primary key values.
If the creator of the table does not specify a primary key, the server creates a primary key for the table automatically. To ensure uniqueness in that primary key, the server uses a hidden internal row identifier. The value of that row identifier may be retrieved and used in queries by way of a symbolic pseudo column name ROWID.
Note Always create a primary key at table creation. In solidDB®, you cannot add an explicit primary key after the table has been created. If a primary key is not specified by a user, the most efficient query method is not available (unless ROWID is used) for that table. Also, such a table cannot be used in referential integrity constraints as a referenced table.
Once a primary key is defined (whether by the table creator or by the server), the server prevents rows with duplicate primary key values from being inserted into the table.