Skip to main content

RowID Field

RowID Field

In SQL, every record is identified by a unique integer value, known as the RowID. In InterSystems SQL you do not need to specify a RowID field. When you create a table and specify the desired data fields, a RowID field is automatically created. This RowID is used internally, but is not mapped to a class property. By default, its existence is only visible when a persistent class is projected to an SQL table. In this projected table, an additional RowID field appears. By default, this field is named "ID" and is assigned to column 1.

By default, when a table is populated with data, InterSystems IRIS assigns sequential positive integers to this field, starting with 1. The RowID data type is BIGINT (%Library.BigIntOpens in a new tab). The values generated for the RowID have the following constraints: Each value is unique. The NULL value is not permitted. Collation is EXACT. By default, values are not modifiable.

By default, InterSystems IRIS names this field “ID”. However this field name is not reserved. The RowID field name is re-established each time the table is compiled. If the user defines a field named “ID”, when the table is compiled InterSystems IRIS names the RowID as “ID1”. If, for example, the user then uses ALTER TABLE to define a field named “ID1”, the table compile renames the RowID as “ID2”, and so forth. In a persistent class definition you can use the SqlRowIdName class keyword to directly specify the RowID field name for the table to which this class is projected. For these reasons, referencing the RowID field by name should be avoided.

InterSystems SQL provides the %ID pseudo-column name (alias) which always returns the RowID value, regardless of the field name assigned to the RowID. (InterSystems TSQL provides the $IDENTITY pseudo-column name, which does the same thing.)

ALTER TABLE cannot modify or delete the RowID field definition.

When records are inserted into the table, InterSystems IRIS assigns each record an integer ID value. RowID values always increment. They are not reused. Therefore, if records have been inserted and deleted, the RowID values will be in ascending numeric sequence, but may not be numerically contiguous.

  • By default, a table defined using CREATE TABLE performs ID assignment using $SEQUENCE, allowing for the rapid simultaneous populating of the table by multiple processes. When $SEQUENCE is used to populate the table, a sequence of RowID values is allocated to a process, which then assigns them sequentially. Because concurrent processes are assigning RowIDs using their own allocated sequences, records inserted by more than one process cannot be assumed to be in the order of insert.

    You can configure InterSystems IRIS to perform ID assignment using $INCREMENT by setting the SetOption()Opens in a new tab method DDLUseSequence option; to determine the current setting, call the $SYSTEM.SQL.CurrentSettings()Opens in a new tab method.

  • By default, a table defined by creating a persistent class performs ID assignment using $INCREMENT. In a persistent class definition, the IdFunction storage keyword can be set to either sequence or increment; for example, <IdFunction>sequence</IdFunction>.

In a persistent class definition, the IdLocation storage keyword global (for example, for persistent class Sample.Person: <IdLocation>^Sample.PersonD</IdLocation>) contains the highest assigned value of the RowID counter. (This is the highest integer assigned to a record, not the highest allocated to a process.) Note that this RowID counter value may no longer correspond to an existing record. To determine if record with a specific RowID value exists, invoke the table’s %ExistsId() method.

The RowID counter is reset by the TRUNCATE TABLE command. It is not reset by a DELETE command, even when the DELETE command deletes all rows in the table. If no data has been inserted into the table, or TRUNCATE TABLE has been used to delete all table data, the IdLocation storage keyword global value is undefined.

By default, RowID values are not user-modifiable. Attempting to modify a RowID value generates an SQLCODE -107 error. Overriding this default to allow modifying of RowID values can have serious consequences and should only be done in very specific cases and with extreme caution. The Config.SQL.AllowRowIDUpdateOpens in a new tab property allows RowID values to be user-modifiable.

RowID Based on Fields

By defining a persistent class that projects a table, you can define the RowID to have values from a field or a combination of fields. To do this, specify an index with the IdKey index keyword. For example, a table can have a RowID whose values are the same as the values of the PatientName field by specifying the index definition IdxId On PatientName [IdKey];, or the combined values of the PatientName and SSN fields by specifying the index definition IdxId On (PatientName,SSN) [IdKey];.

  • A RowID based on fields is less efficient than a RowId that takes system-assigns sequential positive integers.

  • On INSERT: The values specified for the field or combination of fields that make up the RowId must be unique. Specifying a non-unique value generates an SQLCODE -119 “UNIQUE or PRIMARY KEY constraint failed uniqueness check upon INSERT”.

  • On UPDATE: By default, the values of each of the fields that makes up the RowId are non-modifiable. Attempting to modify the value of one of these fields generates an SQLCODE -107 “Cannot UPDATE RowID or RowID based on fields”.

When a RowID is based on multiple fields, the RowID value is the values of each of its component fields joined by the || operator. For example, Ross,Betsy||123-45-6789. InterSystems IRIS attempts to determine the maximum length of a RowID that is based on multiple fields; if it cannot determine the maximum length, the RowID length defaults to 512.

For further details, refer to Primary Key.

RowID Hidden?

  • When using CREATE TABLE to create a table, the RowID is hidden by default. A hidden field is not displayed by SELECT * and is PRIVATE. When you create a table you can specify the %PUBLICROWID keyword to make the RowID not hidden and public. This optional %PUBLICROWID keyword can be specified anywhere in the CREATE TABLE comma-separated list of table elements. It cannot be specified in ALTER TABLE. For further details, refer to The RowID Field and %PUBLICROWID in the CREATE TABLE reference page.

  • When creating a persistent class that projects as a table, the RowID is not hidden by default. It is displayed by SELECT * and is PUBLIC. You can define a persistent class with a RowID that is hidden and PRIVATE by specifying the class keyword SqlRowIdPrivate.

A RowID used as a foreign key reference must be public.

By default, a table with a public RowID cannot be used as either source or destination table to copy data into a duplicate table using INSERT INTO Sample.DupTable SELECT * FROM Sample.SrcTable.

You can display whether the RowID is hidden using the Management Portal SQL interface Catalog Details Fields listing Hidden column.

You can use the following program to return whether a specified field (in this example, ID) is hidden:

  SET myquery = "SELECT FIELD_NAME,HIDDEN FROM %Library.SQLCatalog_SQLFields(?) WHERE FIELD_NAME='ID'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data" 

To list the field names (hidden and non-hidden) in a table, refer to “Column Names and Numbers”.

FeedbackOpens in a new tab