Keys and constraints are used as a way to ensure that our data meets certain require‐ ments prior to being stored in the database. The objects that represent keys and con‐ straints can be found inside the base SQLAlchemy module, and three of the more common ones can be imported as shown here:

from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint

The most common key type is a primary key, which is used as the unique identifier for each record in a database table and is used used to ensure a proper relationship between two pieces of related data in different tables. As you saw earlier in Example 1-1 and Example 1-2, a column can be made a primary key simply by using the primary_key keyword argument. You can also define composite primary keys by assigning the setting primary_key to True on multiple columns. The key will then essentially be treated like a tuple in which the columns marked as a key will be present in the order they were defined in the table. Primary keys can also be defined after the columns in the table constructor, as shown in the following snippet. You can add multiple columns separated by commas to create a composite key. If we wanted to explicitly define the key as shown in Example 1-2, it would look like this:

PrimaryKeyConstraint('user_id', name='user_pk')

Another common constraint is the unique constraint, which is used to ensure that no two values are duplicated in a given field. For our online cookie delivery service, for example, we would want to ensure that each customer had a unique username to log into our systems. We can also assign unique constraints on columns, as shown before in the username column, or we can define them manually as shown here:

UniqueConstraint('username', name='uix_username')

Not shown in Example 1-2 is the check constraint type. This type of constraint is used to ensure that the data supplied for a column matches a set of user-defined criteria. In the following example, we are ensuring that unit_cost is never allowed to be less than 0.00 because every cookie costs something to make (remember from Economics 101: TINSTAAFC—that is, there is no such thing as a free cookie!):

CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive')

In addition to keys and constraints, we might also want to make lookups on certain fields more efficient. This is where indexes come in.