2. CREATING TABLES
2.1.3: Primary Keys
This section assumes you have created the Department and Course tables in the MyUniversity database. Every table should have a primary key, but this is just a rule-of- thumb that most database designers follow. In our database:
-
-
- The Department table has deptCode as its primary key.
- The Course table has a composite primary key – a key formed using two attributes: deptCode and courseNo.
-
To set a primary key the table must be open in Design View. You must first select the field (or combination of fields) and then click the Primary Key icon. This is straightforward for the Department table, but not for the Course table because its’ primary key comprises two fields. Because the PK involves more than one field we say this primary key is composite.
Exercises
1) Set the primary key for the Department table. With the Department table in Design View, select the deptCode field and then right-click and choose Primary Key. When done successfully you will see the deptCode field with a key icon beside it:
- If MS Access rejects your primary key, then you must examine the values you previously entered for deptCode – there must be some duplicated value. If this happens you must view the table in Datasheet View and find the duplicated value and make necessary changes.
- Once MS Access has accepted your primary key you should open the table in datasheet view and experiment: How does MS Access respond if you try to create a new row with an existing primary key value?
2) Set the composite primary key for the Course table. To do this you first select one field, and then while holding the Control key down select the other field. With both fields selected and the Control key down, right-click and select Primary Key:
- Select the deptCode (click to the left of the deptCode field).
- To select the next field to be part of the PK: while holding the Control key down, click the courseNo field
- Now, still with the Control key down, right-click and you will see options
- Choose Primary Key. You will now see the key image beside both fields as in:
- If MS Access rejects your primary key, then you must examine the values you previously entered for deptCode and courseNo – there must be some duplicated value (two or more rows have the same pair of values for deptCode and courseNo). If this happens open the table in Datasheet View and examine the rows to find duplicate values of the combination {deptCode, courseNo}.
- Once MS Access has accepted your primary key you should open the table in datasheet view and experiment: How does MS Access respond if you try to create a new row with an existing primary key value?
3) (Advanced) Later on we discuss relationships between tables. Perhaps you are willing to try this now. The Department and Course tables are related to one another through the deptCode field. It is reasonable for us to expect that a deptCode value in a row of the Course table also appears in a row of the Department table. That is, if we are recording a course for the mathematics department then we expect the database to have a corresponding row in the Department table. To ensure this is the case we create a formal relationship between these two tables using the Relationships Tool:
- First, click Database Tools. Then click Relationships:
- The Relationships Tool opens and you see a blank relationships diagram:
- Drag the Department and Course tables from the Tables List to the diagram:
- With both tables showing on the diagram, you must select the PK of Department, drag it to the Course table, and release the mouse button above the deptCode field of Course. If you follow the directions on the screen you will be able to select enforce referential integrity (RI) and then you end up with the following:
- If RI is enforced, then it becomes impossible to have a row in Course without a matching row in Department.
You can compare your MyUniversity database to the University database provided on the web page for these notes.
