Identity Column Constraints

SourcePro DB : DB Interface Module User’s Guide : PART II Using the Basic Features : The Database Classes : Table Constraints : Column Level Constraints : Identity Column Constraints

A column’s identity constraint simply auto-generates values when a row is inserted into the column. The first record added is assigned the start with value you provide when you create the constraint object. You also define a value to increment by, as well as a maximum and minimum value.

A table can have just one identity column and it cannot have a NULL value. When you insert a new row into a table with an identity column, allow the database to provide the value rather than specifying a value. In general, provide values for identity columns only if an application requires a specific value. Many database vendors prohibit inserting or modifying an explicit value for an identity column under default settings.

A common misconception is that an identity constraint on a column enforces uniqueness, but this is not the case. To enforce a unique value for an identity constraint column, add an additional constraint, either a primary key or a unique constraint.

Class RWDBIdentityConstraint encapsulates identity attributes for a column and is used by RWDBColumn to create an identity constraint. To return an identity constraint, use methods on either RWDBColumn or RWDBTable.

NOTE: 	Note that identity columns are not supported by all database vendors and those that do may not support all possible identity constraint attributes. See the relevant Access Module guide for information specific to your database.

Creating an identity column

To create an identity constraint, create an RWDBIdentityConstraint instance with needed attributes and add it to a column. You can create an identity column when you first create a table, or you can add it later. This example creates a new table with an identity column.

RWDBColumn identityCol; //1

identityCol.name("col1").type(RWDBValue::Decimal);

identityCol.precision(30).scale(0).nullAllowed(false);

RWDecimalPortable dp("1"); //2

RWDBIdentityConstraint idConstr; //3

idConstr.startWith(dp);

idConstr.incrementBy(1);

idConstr.maxValue(1000000);

idConstr.minValue(1);

identityCol.setIdentity(idConstr); //4

RWDBSchema schema;

schema.appendColumn(identityCol);

// append more columns to schema as appropriate

myDbase.createTable("myTable", schema); //5

//1 Creates an instance of RWDBColumn to be the identity column, and sets some attributes.

//2 Creates an RWDecimalPortable as the start value for the identity constraint.

//3 Creates the identity constraint object, setting the startwith, min, increment, and max attributes.

//4 Sets the constraint on the column using the setIdentity() method.

//5 Appends the column to the schema.

//6 Creates a table based on the schema. myDbase is assumed to be a valid RWDBDatabase object.

To return a table’s identity constraint or column, use methods on either RWDBColumn or RWDBTable:

RWDBIdentityConstraint RWDBColumn::getIdentity() const

RWDBColumn RWDBTable::identityColumn() const

For example, to access a table's identity column from a schema fetched from a database:

RWDBTable table = myDbase.table("myTable");

table.describe(RWDBTable::IdentityConstraint);

RWDBColumn idColumn = table.identityColumn();

If (idColumn.isValid()) {

// Retrieve identity attributes from idConstr

RWDBIdentityConstraint idConstr = idColumn.getIdentity();

}

NOTE: 	Some database vendors do not support the concept of an identity column, and attempts to set or otherwise manipulate them are ignored. Please see your relevant Access Module for information regarding constraint support.