4.5 Table Constraints
The DB Interface Module supports a range of SQL table constraints used to enforce rules on the data allowed in a table. If a user attempts to insert, update or delete data that does not conform to the requirements of the constraint, it is rejected. This ensures the integrity and reliability of the data.
Constraints can be assigned on a column or table level. Column level constraints apply only to one column, while table level constraints can apply to multiple columns or the whole table.
Column level constraints supported by the DB Interface Module include not null constraints, identity column constraints and default column constraints. At the table level, the module supports unique table constraints, check constraints, primary key constraints, and foreign key constraints.
4.5.1 Table Constraints Classes
In addition to the multiple methods on various database classes, specific classes that support table constraints include:
Figure 3 illustrates the architecture of the constraint classes.
4.5.2 Column Level Constraints
Constraints that can be set only on columns include not null constraints, default value constraints, and identity column constraints.
4.5.2.1 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.
4.5.2.2 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.
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");
table.fetchSchema();
RWDBSchema sch = table.schema();
RWDBColumn column = sch["col1"];
if (column.hasDefault()) {
RWDBExpr defaultVal = column.defaultValue();
}
4.5.2.3 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 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
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.fetchSchema();
RWDBColumn idColumn = table.identityColumn(); //1
If (idColumn.isValid()) {
// Retrieve identity attributes from idConstr
RWDBIdentityConstraint idConstr = idColumn.getIdentity(); //2
}
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.
4.5.3 Table Level Constraints
This section discusses constraints supported at the table level:
4.5.3.1 Primary Key Constraints
A primary key constraint is a key that uniquely identifies a record in a database and is the primary point of access to that record. Unlike some other constraints, a table can have only one primary key.
Because a primary key constraint ensures unique data, it is frequently used in conjunction with an identity column (
Section 4.5.2.3).
RWDBPrimaryKey encapsulates a primary key. A primary key is composed of a collection of
RWDBColumn instances and must contain at least one column. You can assign a constraint name to the primary key or let the database assign one.
4.5.3.2 Foreign Key Constraints
Class
RWDBForeignKey encapsulates a foreign key constraint and is similar in architecture to
RWDBSchema.
A foreign key in one table references a primary key in another table, linking the tables. For example, the custID primary key in a table Customers could also appear as a custID column in an Invoice table in order to link a specific customer with a specific transaction.
A foreign key constraint ensures
referential integrity between the data in the two tables, meaning that any data in the foreign key column in Invoice must exist in the primary key column in Customers. The table with the foreign key (Invoice) is the “referencing table,” and the table it references (Customers) is, logically, the “referenced table.” For example:
Any changes to a Customers record that would invalidate the link with the corresponding record(s) in Invoice will invoke a particular
referential action, as defined by the enumerator
RWDBForeignKey::Constraint passed to the
RWDBForeignKey constructor and described in
Table 6.
Table 6 – Foreign Key Referential Actions defined in enum RWDBForeignKey::Constraint
Referential Action | Description |
Restrict | Prevents the update or delete operation, resulting in failure. This is the default. |
Cascade | Modifies or deletes the corresponding records in the referencing table, so that the operation invoked on the referenced table is “cascaded” to the referencing table, thus still maintaining referential integrity. |
Nullify | Sets the corresponding rows in the referencing table to have a NULL value for the foreign key. |
Defaultify | Sets the corresponding rows in the referencing table to the default value for the foreign key column(s). |
For example, if updates or deletes to Customers would result in an update or delete of a custID that is referenced by at least one row in the Invoice table, one of the above actions will occur.
An
RWDBForeignKey instance is composed of any columns in the referencing table that are part of the foreign key. There may be one or more columns in a foreign key.
Class
RWDBForeignKeyList holds any number of
RWDBForeignKey instances. An
RWDBSchema object always contains a single
RWDBForeignKeyList containing zero or more foreign key constraints relevant to that schema.
4.5.3.3 Unique Table Constraints
A unique table constraint ensures that a particular column, or set of columns, has a unique value or set of values, thereby uniquely identifying each record in a database table.
The unique and primary key constraints (
Section 4.5.3.1) both provide a guarantee of uniqueness for a column or set of columns, and a primary key constraint automatically has a unique constraint defined on it. A table can have only one primary key constraint, but may have multiple unique constraints.
You can name a unique constraint or let the server assign a name.
Class
RWDBUniqueConstraint encapsulates a unique constraint, and
RWDBUniqueConstraintList holds any number of
RWDBUniqueConstraint instances. An
RWDBSchema object always contains a single
RWDBUniqueConstraintList containing zero or more unique constraints relevant to that schema.
A unique constraint can be comprised of a single column or multiple columns. A unique constraint on multiple columns enforces a unique combination, even if values in individual columns are repeated. For example, consider these two columns, both part of a single unique constraint:
Each of these rows is allowed, because each row’s values form a unique pair between the two columns — but another row with values of 1,1 would be disallowed, as that set is already present.
4.5.3.4 Check Table Constraints
A check constraint limits the values that a column accepts, defined by a logical expression. Any attempt to insert or update a value outside the range of accepted values is rejected.
Check constraints can be specific to one column or multiple columns; further, a single column can have multiple constraints. Defining a check constraint on a table can limit the values for certain columns based on the values of other columns.
Class
RWDBCheckConstraint encapsulates a check constraint based on the expression defined by an
RWDBExpr object.
RWDBCheckConstraintList holds any number of
RWDBCheckConstraint instances. An
RWDBSchema object always contains a single
RWDBCheckConstraintList containing zero or more check constraints relevant to that schema.
4.5.4 Working with Table Level Constraints
You can create a new table and add constraints, or add and drop constraints from an existing table or schema.
This section discusses how to create new constraints and add them to tables at table creation, as well as how to manage existing constraints.
4.5.4.1 Creating Tables with Constraints
To create a table with a constraint, first create a schema, add some columns, and define the constraint on the relevant column using the corresponding method on
RWDBSchema. Then create a table based on the schema.
This section includes examples that illustrate how to create the various types of constraints: primary key, foreign key, check, and unique constraints.
Creating a Primary Key Constraint
To create a primary key constraint, create an
RWDBPrimaryKey instance and pass it to
RWDBSchema::primaryKey(), for example:
RWDBSchema schema; //1
schema.appendColumn("col1", RWDBValue::Int, RWDB_NO_TRAIT, RWDB_NO_TRAIT, //2
RWDB_NO_TRAIT, RWDB_NO_TRAIT, false);
// add more columns to schema as necessary
RWDBPrimaryKey pKey; //3
pKey.appendColumn(schema["col1"]); //4
schema.primaryKey(pKey); //5
myDbase.createTable("myTable", schema); //6
To return a schema’s primary key, call primaryKey() with no argument.
Creating a Foreign Key Constraint
To create and use a foreign key, create an instance of
RWDBForeignKey and pass the constructor the referenced table. This example creates two schemas, one with a primary key and one with a foreign key that references the first schema’s primary key.
First, create the customer schema and define a primary key:
RWDBSchema custSchema; //1
custSchema.appendColumn("pKeyCust", RWDBValue::Int, RWDB_NO_TRAIT,
RWDB_NO_TRAIT, RWDB_NO_TRAIT, RWDB_NO_TRAIT, false);
custSchema.appendColumn("name", RWDBValue::String, 100,
RWDB_NO_TRAIT,RWDB_NO_TRAIT, RWDB_NO_TRAIT, false);
RWDBPrimaryKey pkCust; //2
pkCust.appendColumn( custSchema["pKeyCust"] );
custSchema.primaryKey(pkCust); //3
myDbase.createTable("Customers", custSchema); //4
Now, build the invoice schema and add a primary key:
RWDBSchema invoiceSchema; //5
invoiceSchema.appendColumn("pKeyInv", RWDBValue::Int, RWDB_NO_TRAIT,
RWDB_NO_TRAIT, RWDB_NO_TRAIT, RWDB_NO_TRAIT, false);
invoiceSchema.appendColumn("totalAmt", RWDBValue::Decimal,
RWDB_NO_TRAIT, RWDB_NO_TRAIT, 10,2, false);
invoiceSchema.appendColumn("custID", RWDBValue::Int, RWDB_NO_TRAIT,
RWDB_NO_TRAIT,RWDB_NO_TRAIT, RWDB_NO_TRAIT, false);
// Add more columns relevant to an invoice...
RWDBPrimaryKey pkInv; //6
pkInv.appendColumn( invoiceSchema["pKeyInv"] );
invoiceSchema.primaryKey(pkInv);
Now create the foreign key for the invoice schema:
RWDBForeignKey fKeyToCust("Customers"); //7
fKeyToCust.appendColumn(invoiceSchema["custID"]);
RWDBSchema fKeyToCustRefSch; //8
fKeyToCustRefSch.appendColumn(custSchema["pKeyCust"]);
fkeyToCust.referenceKey(fKeyToCustRefSch); //9
invoiceSchema.foreignKey( fKeyToCust ); //10
myDbase.createTable("Invoice", invoiceSchema); //11
To change the referential action for an update or delete operation, use the
RWDBForeignKey methods
updateConstraint() and
deleteConstraint() and pass in a
Constraint enum value. For instance, to change the action to perform for an update operation to “Defaultify”:
fKeyToCust.updateConstraint(RWDBForeignKey::Defaultify);
To return the name of a foreign key’s referenced table, use the method referenceName().
Creating Unique and Check Constraints
To create a unique or check constraint, first build up a schema with some columns:
RWDBColumn col1, col2, col3; //1
col1.name("col1").type(RWDBValue::Long).nullAllowed(false);
col2.name("col2").type(RWDBValue::Long).nullAllowed(false);
col3.name("col3").type(RWDBValue::Long).nullAllowed(false);
RWDBSchema schema; //2
schema.appendColumn(col1);
schema.appendColumn(col2);
schema.appendColumn(col3);
Line
//1 and following create and define three
RWDBColumn instances, and line
//2 adds them to a schema. Now we’re ready to set some constraints.
Let’s start with unique constraints. Remember that you can create a unique constraint with one or multiple columns. This code creates two constraints, the first set on two columns, and the second on a single column.
RWDBUniqueConstraint uc1; //3
uc1.appendColumn(col1);
uc1.appendColumn(col2);
schema.uniqueConstraint(uc1); //4
RWCString uniqueConstraintName2; //5
RWDBUniqueConstraint uc2 (uniqueConstraintName2);
uc2.appendColumn(col3); //6
schema.uniqueConstraint(uc2);
When you create a unique constraint, you can provide it a name or accept a name assigned by the database.
To add a new check constraint to the table:
RWDBCheckConstraint cc1, cc2; //7
RWCString constraintName1;
cc1.constraintName(constraintName1); //8
cc1.checkCondition(RWDBExpr(col1 < col2)); //9
cc2.checkCondition(RWDBExpr(col2 < col3)); //10
schema.checkConstraint(cc1); //11
schema.checkConstraint(cc2);
Finally, create the table:
myDbase.createTable("myTable", schema);
4.5.4.2 Adding or Dropping Constraints
The DB Interface Module provides various ways to manage your constraints. Methods on
RWDBTable add, drop or fetch constraints for an existing table.
Add or drop constraints from an existing table using the
RWDBTable methods
addConstraint() and
dropConstraint(). These methods are overloaded for each constraint type, i.e. primary key, foreign key, check, and unique constraints.
RWDBStatus addConstraint (const RWDBPrimaryKey &pk)
RWDBStatus addConstraint (const RWDBForeignKey &fk)
RWDBStatus addConstraint (const RWDBCheckConstraint &cc)
RWDBStatus addConstraint (const RWDBUniqueConstraint &uc)
To drop a constraint, use either the constraint name or the constraint object:
RWDBStatus dropConstraint (const RWCString &constraintName)
RWDBStatus dropConstraint (const RWDBPrimaryKey &pk)
RWDBStatus dropConstraint (const RWDBForeignKey &fk)
RWDBStatus dropConstraint (const RWDBCheckConstraint &cc)
RWDBStatus dropConstraint (const RWDBUniqueConstraint &uc)
(Each constraint type method also has an overload that takes an
RWDBConnection for an explicit connection.)
4.5.4.3 Fetching Table Constraints
RWDBTable contains methods to return the various types of constraints. Fetched constraints are stored differently, depending on the type of constraint and whether all or just specific constraints are fetched. A primary key constraint is stored with the schema. Other constraints are appended to the constraint's list object and returned, and are stored in a global cache if one has been defined.
Primary Keys
To return a table’s primary key use:
RWDBPrimaryKey primaryKey ()
Foreign Keys
To fetch a table’s foreign key, use the
RWDBTable method
foreignKeys() and pass in the referenced table name:
RWDBStatus foreignKeys (const RWCString &refName,
RWDBForeignKeyList &keyList)
You can pass in an empty refName to return a list of all foreign keys in a table.
You can also fetch a list of foreign keys that have a given table as their referenced table. For example, to find out which referencing tables contain a foreign key that has the table customers as their referenced table:
RWDBForeignKeyList list;
customers.referredToBy(list);
Unique and Check Constraints
Use methods on
RWDBTable to return a list of a table’s unique or check constraints. These methods take an
RWCString representing the constraint name, and a list object for each constraint type. Each list object is an ordered collection of constraint instances.
RWDBStatus checkConstraints (const RWCString &constraintName,
RWDBCheckConstraintList &list)
RWDBStatus uniqueConstraints (const RWCString &constraintName,
RWDBUniqueConstraintList &list)
Overloads are provided for each constraint type that also take an explicit
RWDBConnection.
If the provided constraint name is empty, the populated list contains all constraints in the table; otherwise, the list contains just the requested constraint. For instance, this code populates a list with all check constraints in the Invoice table:
RWDBTable Invoice = myDbase.table("Invoice");
RWDBCheckConstraintList cList;
RWDBStatus st = Invoice.checkConstraints("", cList);
RWDBSchema also has methods that return constraints as a comma-delimited string to pass when creating SQL statements, for instance, when creating or adding constraints to a table.
RWCString RWDBSchema::checkConstraintsAsString(
const RWDBPhraseBook & pb) const
RWCString RWDBSchema::checkConstraintsAsString() const
RWCString uniqueConstraintsAsString (const RWDBPhraseBook &pb) const
RWCString uniqueConstraintsAsString () const
Note that the methods that take an
RWDBPhraseBook instance produce an access module-specific SQL string. For constraints that are not attached to a particular
RWDBDatabase object, the methods that take no phrasebook object produce a string with generic syntax.
For example, to log the SQL output sent to a database:
RWDBSchema sch;
... // Build up the schema for creating a table
RWFile file("myfile.txt");
// aDbase is assumed to be a valid RWDBDatabase object
file << sch.keysAsString(aDbase.phraseBook());
file << sch.checkConstraintsAsString(aDbase.phraseBook());
file << sch.uniqueConstraintsAsString(aDbase.phraseBook());