Keys and Constraints
Our query in the previous section relied on the fact that our regions and countries were uniquely identified by their names. A column in a table that is used this way is called a key. Ideally, a key’s values should be unique, just like the keys in a dictionary. We can tell the database to enforce this constraint by adding a PRIMARY KEY clause when we create the table. For example, when we created the PopByRegion table, we should have specified the primary key:
| | >>> cur.execute('''CREATE TABLE PopByRegion ( |
| | Region TEXT NOT NULL, |
| | Population INTEGER NOT NULL, |
| | PRIMARY KEY (Region))''') |
Just as a key in a dictionary can be made up of multiple values, the primary key for a database table can consist of multiple columns.
The following code uses the CONSTRAINT keyword to specify that no two entries in the table being created will ever have the same values for region and country:
| | >>> cur.execute(''' |
| | CREATE TABLE PopByCountry( |
| | Region TEXT NOT NULL, |
| | Country TEXT NOT NULL, |
| | Population INTEGER NOT NULL, |
| | CONSTRAINT CountryKey PRIMARY KEY (Region, Country))''') |
In practice, most database designers don’t use real names as primary keys. Instead, they usually create a unique integer ID for each “thing” in the database, such as a driver’s license number or a patient ID. This is partly done for efficiency’s sake—integers are faster to sort and compare than strings—but the real reason is that it is a simple way to deal with things that have the same name. There are a lot of Jane Smiths in the world; using that name as a primary key in a database is almost guaranteed to lead to confusion. Giving each person a unique ID, on the other hand, ensures that they can be told apart.