Working with Table Level Constraints

DB Interface Module User’s Guide : PART II Using the Basic Features : Chapter 4 The Database Classes : Table Constraints : 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

//1 Creates an RWDBSchema instance.

//2 Appends column col1 of type RWDBValue::Int to the schema, disallowing null values.

//3 Creates an RWDBPrimaryKey instance.

//4 Adds the column from schema to the primary key. This makes col1 a primary key.

//5 Sets the schema’s primary key to pKey.

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

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

//1 Creates a customers schema custSchema, adding two columns. The pKeyCust column will be its primary key.

//2 Creates a primary key object and adds the pKeyCust column to it.

//3 Calls the schema’s primaryKey() method to make this the primary key.

//4 Creates the Customers table based on the custSchema schema.

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);

//5 Builds up the invoice schema, adding a primary key column and a custID column that will become the foreign key.

//6 Now creates the primary key for the invoiceSchema in the same way as in lines //2 and //3 for the custSchema.

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

//7 Creates a foreign key and appends a single column custID from the invoiceSchema. This key will be created with the default constraint setting of restrict, meaning that an attempt to update or delete data that would break referential integrity with the referenced table will fail. To create a foreign key and instead set the constraint reference for an update operation to cascade, and a delete operation to nullify, you would do:

RWDBForeignKey fKeyToCust("Customers", RWDBForeignKey::Cascade, RWDBForeignKey::Nullify);

//8 Creates the reference schema to contain any columns from the referenced table Customers. The only referenced column is pKeyCust, so we add that column to the reference schema.

//9 Set the foreign key to reference the primary key column pKeyCust of the Customers table by calling referenceKey() and passing it the reference schema created in line //8.

//10 Adds the foreign key to the schema’s list of foreign keys.

//11 Creates the Invoice table.

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);

//3 Creates the initial unique constraint uc1 and appends the columns col1 and col2 in order to make the combination of both columns unique.

//4 Adds the constraint uc1 defining two unique columns to the schema’s internal list of unique constraints.

//5 Creates a string so we can name a second unique constraint and then creates the constraint. If a name is not provided, the server provides one.

//6 Adds col3 to the constraint and adds it to the schema.

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);

//7 Creates two check constraint objects.

//8 Calls constraintName() to name the constraint constraintName1. Note that this is optional and that the constraint cc2 is not assigned a name. If no name is provided, the server creates one.

//9 Calls checkCondition(), passing it the condition as an RWDBExpr object. Here, the check requires that values in col1 be less than those in col2.

//10 Defines a check condition for constraint cc2.

//11 Adds both check constraints to the schema’s internal list of constraints.

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());