ADO - ADOX Key Object for Primary and Foreign Key Management

The ADOX Key object is part of ADO Extensions for DDL and Security (ADOX). It is used to describe and manage keys associated with database tables. Keys are important in relational databases because they help maintain the uniqueness of records and establish relationships between tables. In ADOX, the Key object provides a way to work with database key definitions programmatically rather than writing SQL statements for every structural operation.

Purpose of the ADOX Key Object

A database key defines a rule or relationship involving one or more columns. The most common types are Primary Key and Foreign Key. A primary key uniquely identifies each record in a table, while a foreign key connects a column in one table to a primary key or unique key in another table.

For example, consider two tables:

Students
---------
StudentID
Name
Email

Courses
-------
CourseID
CourseName

If another table stores student-course registrations:

Enrollments
-----------
EnrollmentID
StudentID
CourseID

EnrollmentID can be the primary key of Enrollments, while StudentID can be a foreign key referencing Students.StudentID. Similarly, CourseID can reference Courses.CourseID.

The ADOX Key object can represent these key definitions and can be added to or removed from a table through the ADOX Keys collection.

Creating a Key with ADOX

A Key object can be created through the ADOX Keys collection. The basic approach is to create a key, specify its name and type, identify the relevant column, and then append it to the table.

A typical VBScript example is:

Dim cat
Dim tbl
Dim key

Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\College.accdb"

Set tbl = cat.Tables("Students")

Set key = CreateObject("ADOX.Key")

key.Name = "PK_Students"
key.Type = 1
key.Columns.Append "StudentID"

tbl.Keys.Append key

Here, the Catalog represents the database, while the Table object represents the Students table. A new Key object is created and given the name PK_Students. The key type identifies it as a primary key, and StudentID is added as the key column.

The exact numeric values used for key types depend on the ADOX enumeration. Using the named ADOX constants in environments that expose them is generally clearer than using numeric values directly.

Primary Keys

A primary key is used to uniquely identify records within a table. A table normally has one primary-key constraint, although that key can contain multiple columns.

For example:

Employees
---------
EmployeeID
EmployeeName
Department

If EmployeeID uniquely identifies every employee, it can be defined as the primary key.

With ADOX, a primary key can be represented using a Key object:

Set key = CreateObject("ADOX.Key")

key.Name = "PK_Employees"
key.Type = 1
key.Columns.Append "EmployeeID"

tbl.Keys.Append key

Once the key is appended, the database provider is responsible for enforcing the primary-key constraint.

Foreign Keys

A foreign key establishes a relationship between two tables. It ensures that a value in the child table corresponds to an appropriate key value in the parent table.

For example:

Departments
-----------
DepartmentID
DepartmentName

Employees
---------
EmployeeID
EmployeeName
DepartmentID

Here, Departments.DepartmentID can be the primary key, while Employees.DepartmentID can be a foreign key referencing it.

An ADOX foreign key can be defined using the Key object:

Set key = CreateObject("ADOX.Key")

key.Name = "FK_Employees_Departments"
key.Type = 2
key.RelatedTable = "Departments"

key.Columns.Append "DepartmentID"
key.Columns("DepartmentID").RelatedColumn = "DepartmentID"

tbl.Keys.Append key

The important difference is that a foreign key requires information about both the local column and the related table and column.

Important Properties

The Key object provides several properties that describe a key.

Name

The Name property specifies the name of the key constraint.

key.Name = "PK_Students"

Giving keys meaningful names makes database structures easier to understand and maintain.

Type

The Type property identifies the type of key. It can represent different key types supported by ADOX, including primary keys, unique keys, and foreign keys.

RelatedTable

For a foreign key, RelatedTable identifies the table containing the referenced key.

key.RelatedTable = "Departments"

Columns

The Columns collection contains the columns that participate in the key.

key.Columns.Append "DepartmentID"

For a foreign key, the relevant column can also contain information about the corresponding column in the related table.

Key Columns Collection

The Columns collection is particularly important because a key can involve one or multiple columns.

A single-column primary key might look like:

key.Columns.Append "StudentID"

A composite key can contain multiple columns:

key.Columns.Append "StudentID"
key.Columns.Append "CourseID"

In this case, the combination of StudentID and CourseID identifies a record rather than either column individually.

Composite keys are useful when a relationship or record is naturally identified by multiple attributes.

Removing a Key

ADOX also allows an existing key to be removed from a table through the Keys collection.

For example:

tbl.Keys.Delete "PK_Students"

This removes the specified key definition from the table. Removing a primary or foreign key should be done carefully because other database objects or application logic may depend on that constraint.

Why Key Objects Are Important

The ADOX Key object is useful when an application needs to work with database structure dynamically. Instead of limiting database operations to inserting, updating, and retrieving data, an application can also inspect and modify structural elements such as keys.

It is particularly useful for database administration utilities, database-generation applications, migration tools, and programs that create database structures automatically.

ADOX Key Object and Data Integrity

Keys play an important role in data integrity.

A primary key prevents duplicate identifiers and provides a reliable way to identify individual records. A foreign key helps prevent invalid relationships between tables. For example, if an employee references department number 50, but department 50 does not exist, a properly enforced foreign-key constraint can prevent that invalid relationship from being stored.

Therefore, the ADOX Key object is not simply a programming representation of a database key. It provides a programmatic mechanism for defining structural constraints that help maintain the consistency and relationships of relational data.

Key Points

The ADOX Key object:

  • Represents a database key definition.

  • Is part of ADOX rather than the basic ADO Recordset model.

  • Can be used with the table's Keys collection.

  • Supports primary-key definitions.

  • Supports foreign-key definitions.

  • Can represent unique-key constraints supported by the provider.

  • Uses the Columns collection to identify key columns.

  • Uses RelatedTable for foreign-key relationships.

  • Can support composite keys.

  • Can be appended to or deleted from a table.

  • Helps applications manage database structure programmatically.

The main idea is that ADOX Key provides programmatic control over database key definitions, allowing applications to create, inspect, and manage primary and foreign-key relationships as part of database structure management.