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.
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);
NOTE >> 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);
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.)
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.
For detail on constraints and caching, see
“Metadata Caching and the Cache Manager.” Primary Keys
To return a table’s primary key, first get the table’s schema using
RWDBTable’s method
describe(RWDBTable::PrimaryKey); then, on the returned schema, call
RWDBSchema::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());