Creating and Deleting Relationships by Using SQL DDL

In the section “Creating and Modifying Table Indexes,” one form of the CONSTRAINT clause used in the SQL DDL language was discussed. In those examples, CONSTRAINT clauses are used to create single-field or multifield indexes for a table, either at the time the table is created (with CREATE TABLE) or after the fact (with ALTER TABLE).

Another use of the CONSTRAINT clause is to create a relationship (or reference) from a foreign key of one table to the primary key of another table. With this constraint in place, it’s impossible to enter a value in the foreign key field of a table that doesn’t already exist in the primary key field of the primary table.

In the following example, two indexes are created. The first is the primary key index for TaskID, and the second is a foreign key index for the EmployeeID field:

CREATE TABLE Tasks
	(TaskID INTEGER CONSTRAINT PrimaryKey PRIMARY KEY,
		EmployeeID INTEGER
	CONSTRAINT FK_EmployeeID REFERENCES Employees
		(EmployeeID),
	DeptID TEXT (10),
	TaskCompleted DateTime);

The next example creates a new table called Departments. The DeptID field is the primary key for the Departments table. The DeptID field is the foreign key in the Tasks table used in the example above:

CREATE TABLE Departments
	(DeptID TEXT (10) CONSTRAINT PrimaryKey PRIMARY KEY,
	Description TEXT (50) );

When these statements are executed, Microsoft Jet creates two new indexes, PrimaryKey and FK_EmployeeID, in the Indexes collection of the newly created Tasks TableDef object. In addition to the new foreign key indexes, a new Relation object is created in the database. As discussed in Chapter 2, “Introducing Data Access Objects,” a database contains a Relations collection, which stores information about all the inter-table relationships defined for your database. Each Relation object contains several properties, such as Table and ForeignTable, which identify the tables that participate in the relationship. Each Relation object also contains a Fields collection, which identifies the Field objects that make up the Relation object.

You can create a relationship for an existing table by using the ALTER TABLE statement. The following example creates a new relationship between the Tasks table and the Departments table:

ALTER TABLE Tasks
	ADD CONSTRAINT FK_DeptID 
	FOREIGN KEY (DeptID) REFERENCES Departments (DeptID);

Setting a referential integrity foreign key constraint is the only form of relationship that can be established through SQL DDL. To set other Relation object types, such as cascading updates or cascading deletes, you must create the Relation object through DAO.

There is no way to modify an existing relationship. You must delete the relationship and then re-create it.

To delete a relationship by using SQL DDL, use an ALTER TABLE statement with the DROP CONSTRAINT clause. The following SQL DDL statement deletes the relationship created between the Tasks table and the Departments table in the previous example:

ALTER TABLE Tasks
	DROP CONSTRAINT FK_DeptID