Using SQL Server Identity Columns

SQL Server and similar databases implement a feature called identity columns. A single column within a table may be configured as the identity column, and if this is done, SQL Server automatically inserts values for this column as needed using a unique identifier. When performing an INSERT into a table using an identity column, that column is omitted entirely from the statement. SQL Server automatically generates the new identifier and inserts it.

In some cases, this can be very useful to ensure unique identifiers are generated for a particular table. While SQL Server does not guarantee that the values are continuous (that is, there may be gaps in the numbers that are allocated), it does guarantee that the values are unique. Frequently these identity columns are used to generate a unique identifier for some data, and then this identifier is subsequently used as a foreign key when inserting into other tables that are related in some way.

Using identity columns in the Rhapsody Database Configuration Editor is fairly simple. If the newly allocated identifier is not needed, then inserting the value is simple. For example, consider a NextOfKin table with seven columns including an identity column for the identifier. If the new identifier is not needed, the following script is all that is needed:

INSERT INTO NextOfKin(PatientID, FamilyName, GivenName, Sex, Relationship, PhoneNumber)
  VALUES (
    @PID.PatientID.IDNumber,
    @NKl.NextOfKinName[0].FamilyName.Surname,
    @NKl.NextOfKinName[0].GivenName,
    @NKl.Sex,@NKl.ContactRole.Identifier,
    @NKl.PhoneNumber[0].PhoneNumber.PhoneNumber
)

Alternatively, if the newly allocated identifier is wanted, then it can be immediately retrieved by performing a SELECT on the special @@IDENTITY value in SQL Server, as shown by the following script:

INSERT INTO NextOfKin(PatientID, FamilyName, GivenName, Sex, Relationship, PhoneNumber)
  VALUES (
    @PID.PatientID.IDNumber,
    @NKl.NextOfKinName[0].FamilyName.Surname,
    @NKl.NextOfKinName[0].GivenName,
    @NKl.Sex,@NKl.ContactRole.Identifier,
    @NKl.PhoneNumber[0].PhoneNumber.PhoneNumber
);

SELECT @@IDENTITY AS NextOfKinID

This example inserts the values into the table, then immediately retrieves the newly allocated identifier. This identifier is then available for subsequent statements to use, they would reference it as @NextOfKinID, or for Result Substitution.