Navigation:  Samples > Relation Sample

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.

 

FUNCTION Start

LOCAL dbeng        AS DaoDBEngine

LOCAL db        AS DaoDatabase

LOCAL qdfTest        AS DaoRelation

LOCAL tdfReferenced        AS DaoTableDef

LOCAL tdfReferencing        AS DaoTableDef

LOCAL fldPrimeKey        AS DaoField

LOCAL idxUnique        AS DaoIndex

LOCAL relEnforced        AS DaoRelation

LOCAL nFld AS        LONG

LOCAL oErr        AS USUAL

LOCAL cbOldErr AS CODEBLOCK

Set COlor TO w+/b

cls

BEGIN SEQUENCE

 cbOldErr := ErrorBlock({|oErr|_Break(oErr)})

 dbEng        := DaoDbengine{}

 

 

 // Open the database.

 db := dbEng:OpenDatabase("\VO2Jet\Northwind.mdb",NIL,NIL,NIL)

 

 // Create referenced table with primary key.

 tdfReferenced        := db:CreateTableDef("Referenced",NIL,NIL,NIL)

 

 fldPrimeKey        := tdfReferenced:CreateField("PrimaryKey", dbLong,NIL)

 tdfReferenced:Fields:Append(fldPrimeKey)

 

 // Create unique index for enforced referential integrity.

 idxUnique := tdfReferenced:CreateIndex("UniqueIndex")

 idxUnique:Primary := TRUE // No Null values allowed.

 

 fldPrimeKey := tdfReferenced:CreateField("PrimaryKey",NIL,NIL)

 idxUnique:Fields:Append(fldPrimeKey)

 tdfReferenced:Indexes:Append(idxUnique)

 db:TableDefs:Append(tdfReferenced)

 

 // Create referencing table with foreign key.

 

 tdfReferencing := db:CreateTableDef("Referencing",NIL,NIL,NIL)

 fldPrimeKey := tdfReferencing:CreateField("ForeignKey", dbLong,NIL)

 tdfReferencing:Fields:Append(fldPrimeKey)

 db:TableDefs:Append(tdfReferencing)

 

 // Create one-to-many relationship & enforce referential integrity.

 relEnforced := db:CreateRelation("EnforcedOneToMany",NIL,NIL,NIL)

 relEnforced:Table := "Referenced"

 relEnforced:ForeignTable := "Referencing"

 

 // Don't set either dbRelationUnique or dbRelationDontEnforce.

 relEnforced:Attributes := _OR(DbRelationUpdateCasCade+DbRelationDeleteCasCade,dbRelationRight)

 fldPrimeKey := relEnforced:CreateField("PrimaryKey",NIL,NIL)

 

 fldPrimeKey:ForeignName := "ForeignKey"

 relEnforced:Fields:Append(fldPrimeKey)

 db:Relations:Append(relEnforced)

 

 // Enumerate relation and its fields.

 ? "Relation: ", relEnforced:Name

 ? "  Primary Table: ", relEnforced:Table

 ? "  Foreign Table: ", relEnforced:ForeignTable

 ? "  Attributes   : ", DaoEnum2Str(relEnforced:Attributes,DaoRelationAttributeEnum(),TRUE)

 ? "Fields in Relation: Primary, Foreign"

 FOR nFld = 1 TO relEnforced:Fields:Count STEP 1

     fldPrimeKey := relEnforced:Fields:[Item,nFld]

 

     ? fldPrimeKey:Name, fldPrimeKey:ForeignName

 NEXT

 wait

 db:Relations:Delete("EnforcedOneToMany")

 db:TableDefs:Delete("Referenced")

 db:TableDefs:Delete("Referencing")

RECOVER USING oErr

 Eval(cbOldErr,oErr)

END