Creating Tables with Constraints

SourcePro DB : DB Interface Module User’s Guide : PART II Using the Basic Features : The Database Classes : Table Constraints : Working with Table Level 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);