Column Level Constraints

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

Constraints that can be set only on columns include not null constraints, default value constraints, and identity column constraints.

Not Null Constraints

By default, an RWDBColumn can hold NULL values. You can override this default behavior by setting a not null constraint to enforce that a given column always have a value. Use method RWDBColumn::nullAllowed() and pass in the bool value false to set this constraint on a column.

This code creates a schema with two columns, one that allows NULL values and one that does not:

RWDBSchema schema;

RWDBColumn col1, col2;

col1.type(RWDBValue::String).nullAllowed(true);

col1.name("col1").storageLength(100);

col2.type(RWDBValue::String).nullAllowed(false); //1

col2.name("col2").storageLength(100000);

schema.appendColumn(col1);

schema.appendColumn(col2);

myDbase.createTable("myTestTbl", schema);

Line //1 sets the not null constraint on col2.

To return the value of a column’s nullAllowed attribute, call RWDBColumn::nullAllowed() with no argument.

Default Column Constraints

A default column constraint enforces a value to use when a row is inserted into a table and no value is provided for a column. The value can be a constant or the result of a SQL function, depending on the vendor.

NOTE >>	SourcePro DB currently does not support stored procedure creation with a default value for a parameter or fetching the default value of a stored procedure parameter.

Use the defaultValue() method on RWDBColumn to set or unset default values. This method takes an RWDBExpr object containing the value. For example, to set a value:

RWDBExpr expr(10); //1

RWDBSchema schema;

RWDBColumn col;

... // set some other column attributes

col.defaultValue(expr); //2

schema.appendColumn(col);

myDbase.createTable("myTestTbl", schema);

Line //1 creates an RWDBExpr object with the value 10, and line //2 sets the default value on the column.

To unset a default value, pass an empty RWDBExpr, like so:

col.defaultValue(RWDBExpr());

Use hasDefault() to see if a default value has been set:

bool hasDefault = col.hasDefault();

For example, to fetch a column’s default value as defined in a schema fetched from the database:

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

RWDBSchema sch = table.describe(RWDBTable::DefaultValues);

RWDBColumn column = sch["col1"];

if (column.hasDefault()) {

RWDBExpr defaultVal = column.defaultValue();

}

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.