How to Use Keys to Access Information Quickly in a SQL Database
A good rule for SQL database design is to make sure that every row in a database table is distinguishable from every other row; each row should be unique. Sometimes you may want to extract data from your database for a specific purpose (such as a statistical analysis), and in doing so, end up creating tables in which the rows aren’t necessarily unique.
For such a limited purpose, this sort of duplication doesn’t matter. Tables that you may use in more than one way, however, should not contain duplicate rows.
A key is an attribute (or combination of attributes) that uniquely identifies a row in a table. To access a row in a database, you must have some way of distinguishing that row from all the other rows. Because keys must be unique, they provide such an access mechanism.
Furthermore, a key must never contain a null value. If you use null keys, you may not be able to distinguish between two rows that contain a null key field.
Consider an example of a veterinary-lab: you can designate appropriate columns as keys. In the CLIENT table, ClientName is a good key. This key can distinguish each individual client from all other clients. Therefore entering a value in this column becomes mandatory for every row in the table.
TestName and EmployeeName make good keys for the TESTS and EMPLOYEE tables. OrderNumber and ResultNumber make good keys for the ORDERS and RESULTS tables. Make sure that you enter a unique value for every row.
You can have two kinds of keys: primary keys and foreign keys.
A primary key is a column or combination of columns in a table with values that uniquely identify the rows in the table. To incorporate the idea of keys into the VetLab database, you can specify the primary key of a table as you create the table. In the following example, a single column is sufficient (assuming that all of VetLab’s clients have unique names):
CREATE TABLE CLIENT ( ClientName CHAR (30) PRIMARY KEY, Address1 CHAR (30), Address2 CHAR (30), City CHAR (25), State CHAR (2), PostalCode CHAR (10), Phone CHAR (13), Fax CHAR (13), ContactPerson CHAR (30) ) ;
The constraint PRIMARY KEY replaces the constraint NOT NULL, given in the earlier definition of the CLIENT table. The PRIMARY KEY constraint implies the NOT NULL constraint, because a primary key can’t have a null value.
Although most DBMSs allow you to create a table without a primary key, all tables in a database should have one. With that in mind, replace the NOT NULL constraint in all your tables. In the example, the TESTS, EMPLOYEE, ORDERS, and RESULTS tables should have the PRIMARY KEY constraint, as in the following example:
CREATE TABLE TESTS ( TestName CHAR (30) PRIMARY KEY, StandardCharge CHAR (30) ) ;
Sometimes no single column in a table can guarantee uniqueness. In such cases, you can use a composite key — a combination of columns that guarantee uniqueness when used together. Imagine that some of VetLab’s clients are chains that have offices in several cities. ClientName isn’t sufficient to distinguish between two branch offices of the same client. To handle this situation, you can define a composite key as follows:
CREATE TABLE CLIENT ( ClientName CHAR (30) NOT NULL, Address1 CHAR (30), Address2 CHAR (30), City CHAR (25) NOT NULL, State CHAR (2), PostalCode CHAR (10), Phone CHAR (13), Fax CHAR (13), ContactPerson CHAR (30), CONSTRAINT BranchPK PRIMARY KEY (ClientName, City) ) ;
As an alternative to using a composite key to uniquely identify a record, you can let your DBMS assign one automatically, as Access does in suggesting that the first field in a new table be named ID and be of the Autonumber type. Such a key has no meaning in and of itself. Its only purpose is to be a unique identifier.
A foreign key is a column or group of columns in a table that corresponds to or references a primary key in another table in the database. A foreign key doesn’t have to be unique, but it must uniquely identify the column(s) in the particular table that the key references.
If the ClientName column is the primary key in the CLIENT table (for example), every row in the CLIENT table must have a unique value in the ClientName column. ClientName is a foreign key in the ORDERS table. This foreign key corresponds to the primary key of the CLIENT table, but the key doesn’t have to be unique in the ORDERS table.
In fact, you hope the foreign key isn’t unique; if each of your clients gave you only one order and then never ordered again, you’d go out of business rather quickly. You hope that many rows in the ORDERS table correspond with each row in the CLIENT table, indicating that nearly all your clients are repeat customers.
The following definition of the ORDERS table shows how you can add the concept of foreign keys to a CREATE statement:
CREATE TABLE ORDERS ( OrderNumber INTEGER PRIMARY KEY, ClientName CHAR (30), TestOrdered CHAR (30), Salesperson CHAR (30), OrderDate DATE, CONSTRAINT NameFK FOREIGN KEY (ClientName) REFERENCES CLIENT (ClientName), CONSTRAINT TestFK FOREIGN KEY (TestOrdered) REFERENCES TESTS (TestName), CONSTRAINT SalesFK FOREIGN KEY (Salesperson) REFERENCES EMPLOYEE (EmployeeName) ) ;
In this example, foreign keys in the ORDERS table link that table to the primary keys of the CLIENT, TESTS, and EMPLOYEE tables.