Relation Object, Relations Collection Example (VC++)

This example creates a one-to-many relationship between single fields in two tables. Because referential integrity is enforced for the relationship, an index is created for the primary table. See the methods and properties listed in the Relation summary topic for additional examples.

CdbDBEngine   dbeng;
CdbDatabase   db;
CdbRelation   qdfTest;
CdbTableDef   tdfReferenced, tdfReferencing;
CdbField   fldPrimeKey;
CdbIndex   idxUnique;
CdbRelation   relEnforced;
int         nFld;

// Open the database.
db = dbeng.OpenDatabase(_T("Northwind.mdb"));

// Create referenced table with primary key.
tdfReferenced = db.CreateTableDef(_T("Referenced"));
fldPrimeKey = tdfReferenced.CreateField(_T("PrimaryKey"), dbLong);
tdfReferenced.Fields.Append(fldPrimeKey);

// Create unique index for enforced referential integrity.
idxUnique = tdfReferenced.CreateIndex(_T("UniqueIndex"));
idxUnique.SetPrimary(TRUE); // No Null values allowed.

fldPrimeKey = tdfReferenced.CreateField(_T("PrimaryKey"));
idxUnique.Fields.Append(fldPrimeKey);
tdfReferenced.Indexes.Append(idxUnique);
db.TableDefs.Append(tdfReferenced);

// Create referencing table with foreign key.
tdfReferencing = db.CreateTableDef(_T("Referencing"));
fldPrimeKey = tdfReferencing.CreateField(_T("ForeignKey"), dbLong);
tdfReferencing.Fields.Append(fldPrimeKey);
db.TableDefs.Append(tdfReferencing);

// Create one-to-many relationship & enforce referential integrity.
relEnforced = db.CreateRelation(_T("EnforcedOneToMany"));
relEnforced.SetTable(_T("Referenced"));
relEnforced.SetForeignTable(_T("Referencing"));

// Don't set either dbRelationUnique or dbRelationDontEnforce.
relEnforced.SetAttributes(0);
fldPrimeKey = relEnforced.CreateField(_T("PrimaryKey"));
fldPrimeKey.SetForeignName(_T("ForeignKey"));
relEnforced.Fields.Append(fldPrimeKey);
db.Relations.Append(relEnforced);

// Enumerate relation and its fields.
printf("Relation: %s\n", relEnforced.GetName());
printf("  Primary Table: %s\n", relEnforced.GetTable());
printf("  Foreign Table: %s\n", relEnforced.GetForeignTable());
printf("  Attributes: %ld\n", relEnforced.GetAttributes());
printf("Fields in Relation: Primary, Foreign");
for (nFld = 0; nFld < relEnforced.Fields.GetCount(); nFld++)
   {
   fldPrimeKey = relEnforced.Fields[nFld];
   printf("  %s", fldPrimeKey.GetName());
   printf(", %s\n", fldPrimeKey.GetForeignName());
   }

db.Relations.Delete("EnforcedOneToMany");
db.TableDefs.Delete("Referenced");
db.TableDefs.Delete("Referencing");