Keys and constraints

Every table declares its primary key with the pkey argument of table. pkey names one column. In the invoice table the key is id. The recipe does not declare id as a column. The sys_fields option adds it, together with other system columns. Lesson 25 covers sys_fields. A list of names separated by commas is not accepted. AsqueelDb raises a ValueError that says the pkey is not a column or a compositeColumn. A key made of several columns is declared through a compositeColumn, which the next scene shows. How asqueel generates key values on insert is the subject of lesson 20.

A compositeColumn packs several physical columns of the same table under one name. It does not create a column in the database. Here product_year_key packs product_id and year. With unique True, migration creates one UNIQUE constraint over both columns. A price can exist once per product and year. The name product_year_key is also a key that a relation can point to, and a name that pkey can use. When pkey names a compositeColumn, the primary key of the table is made of its member columns.

A named table constraint goes in the constraints collection of the table. You call constraints, then constraint. The constraint_type is UNIQUE or CHECK. Any other value raises a validation error. A UNIQUE constraint needs columns, a list of names separated by commas. A CHECK constraint needs check_clause, an SQL expression. A missing columns or check_clause raises when the model is built. Unlike column, constraint is not routed for you. Calling constraint directly on the table raises an error that asks for the constraints collection.

Migration compares the clause in the recipe with the clause that PostgreSQL stores. PostgreSQL rewrites a clause into its own normal form. For example, unit_price greater or equal to zero is stored with brackets and a cast to numeric. If the recipe keeps the short text, every later migration sees a difference. It drops the constraint and creates it again. Then migrate raises MigrationError, because differences remain after applying. Write the clause in the form PostgreSQL stores. You can read that form with psql, in the output of backslash d on the table. The same rule applies to the where clause of a partial index.

An index is declared with index on the table. columns is a string of names, a list, or a dictionary. The dictionary maps each name to None or to DESC, for a descending column. name is the index name. Without a name, migration generates one. where makes a partial index. unique makes a unique index. method chooses the access method, and the default is btree. The second index on the slide is descending and partial. The third is unique over two columns of the note table.

A column can ask for its own index with indexed. True creates a plain index. A dictionary carries options. sorting sets the direction. unique without where makes the column unique and creates no index. A column that carries a relation always receives an index. The DDL of the course model shows it, for example on customer_id of the invoice table. Two dictionary options do not work at column level in this release. A where or a method makes migration raise UnsupportedFeatureError. For a partial index or another method, use index on the table, as in the previous scene.

A PostgreSQL extension is declared on the database. You call extensions, then extension with the extension name. Migration creates it with CREATE EXTENSION IF NOT EXISTS. Migration never drops an extension. The extension must be available on the server. Declaring it only asks PostgreSQL to install it in this database.

The primary key names one column or one compositeColumn. Composite columns, constraints and indexes become DDL through migration. Clauses must be written in the form PostgreSQL stores, or every migration re-creates them. The next lesson covers relations.