HandsOn Running Data Definition Statements in the Microsoft Access User Interface

Each of the statements in this hands-on can be executed by selecting Run from the Query menu.

To access the Data Definition Query window, perform the following steps:

1. In the Database window, select the Queries object and click the New button.

2. In the New Query dialog box, select Design View and click OK.

3. In the Show Table dialog box, click the Close button.

4. Choose Query | SQL Specific | Data Definition from the menu bar.

5. To create a table on the Primary (one) side of the relationship, type the following statement and run the query:

CREATE TABLE myPrimaryTbl(ID COUNTER CONSTRAINT pKey PRIMARY KEY, COUNTRY TEXT(15));

Part III

6. To create a table on the Foreign (many) side of the relationship, type the following statement and run the query:

CREATE TABLE myForeignTbl(ID LONG, Region TEXT (15));

7. To create a one-to-many relationship between myPrimaryTbl and myForeignTbl, type the following statement and run the query:

ALTER TABLE myForeignTbl ADD CONSTRAINT Rel FOREIGN KEY(Id) REFERENCES myPrimaryTbl (Id);

8. Switch to the Database window and choose Tools | Relationships.

9. In the Relationships window, choose Relationships | ShowAll. This will add both tables (myPrimaryTbl and myForeignTbl) to the Relationships window (Figure 20-8).

Figure 20-8: Notice that the tables you created by running the DDL statements in steps 5 and 6 above are joined on the ID column (see step 7).

Figure 20-8: Notice that the tables you created by running the DDL statements in steps 5 and 6 above are joined on the ID column (see step 7).

10. Double-click the joining line between the two tables (myPrimaryTbl and myForeignTbl) to display the Edit Relationships window.

Edit Relationships

Table/Query;

Related Tabte/Query;

! myPrimaryTbl

v J myForeignTbl

ho

V ¡ID A|

0 Enforce Referential Integrity |~1 Cascade Update Related Fields □ Cascade Deiete Related Records

0 Enforce Referential Integrity |~1 Cascade Update Related Fields □ Cascade Deiete Related Records

Relationship Type: One-To-Many c

Figure 20-9: You can edit relationships between tables via the Edit Relationships window.

11. To delete the relationship between the tables, type the following statement and run the query:

ALTER TABLE myForeignTbl DROP CONSTRAINT Rel;

12. To delete the table on the one side (myPrimaryTbl), type the following statement and run the query:

DROP TABLE myPrimaryTbl;

Programming with the Jet Data Definition Language

13. To delete the table on the many side (myForeignTbl), type the following statement and run the query:

DROP TABLE myForeignTbl;

0 0

Post a comment