DB Interface Module User’s Guide : PART II Using the Basic Features : Chapter 4 The Database Classes : Table Constraints : Table Level Constraints
Table Level Constraints
This section discusses constraints supported at the table level:
• Primary key constraints, “Primary Key Constraints”
• Foreign key constraints, “Foreign Key Constraints”
• Unique constraints, “Unique Table Constraints”
• Check constraints, “Check Table Constraints”
For code examples on using constraints, see “Working with Table Level Constraints.”
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 (“Identity Column Constraints”).
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.
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.
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 (“Primary Key Constraints”) 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:
COL1
COL2
1
1
1
2
2
2
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.
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.