Constraints

The constraint module defines six classes: Constraint derived from DbSchemaObject, classes CheckConstraint, PrimaryKey, ForeignKey and UniqueConstraint derived from Constraint, and ConstraintDict derived from DbObjectDict.

Constraint

Class Constraint is derived from DbSchemaObject and represents a constraint on a database table. Its keylist attributes are the schema name, the table name and the constraint name.

class pyrseas.dbobject.constraint.Constraint(name, schema='public', description=None, **attrs)

A constraint definition, such as a primary key, foreign key or unique constraint

Constraint.key_columns()

Return comma-separated list of key column names

Returns:string
Constraint.add(obj, *args, **kwargs)

Return string to add the constraint via ALTER TABLE

Returns:SQL statement

Works as is for primary keys and unique constraints but has to be overridden for check constraints and foreign keys.

Constraint.drop()

Return string to drop the constraint via ALTER TABLE

Returns:SQL statement
Constraint.comment()

Return SQL statement to create COMMENT on constraint

Returns:SQL statement

Check Constraint

CheckConstraint is derived from Constraint and represents a CHECK constraint.

class pyrseas.dbobject.constraint.CheckConstraint(name, schema='public', description=None, **attrs)

A check constraint definition

CheckConstraint.to_map(db, dbcols)

Convert a check constraint definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns
Returns:dictionary
CheckConstraint.add(obj, *args, **kwargs)

Return string to add the CHECK constraint via ALTER TABLE

Returns:SQL statement

Primary Key

PrimaryKey is derived from Constraint and represents a primary key constraint.

class pyrseas.dbobject.constraint.PrimaryKey(name, schema='public', description=None, **attrs)

A primary key constraint definition

PrimaryKey.to_map(db, dbcols)

Convert a primary key definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns
Returns:dictionary

Foreign Key

ForeignKey is derived from Constraint and represents a foreign key constraint.

The following shows a foreign key segment of a map returned by to_map() and expected as argument by ConstraintDict.diff_map() exemplifying various possibilities:

{'t1_fgn_key1':
    {
    'columns': ['c2', 'c3'],
    'on_delete': 'restrict',
    'on_update': 'set null',
    'references':
        {'columns': ['pc2', 'pc1'], 'schema': 's1', 'table': 't2'}
    }
}
class pyrseas.dbobject.constraint.ForeignKey(name, schema='public', description=None, **attrs)

A foreign key constraint definition

ForeignKey.ref_columns()

Return comma-separated list of reference column names

Returns:string
ForeignKey.to_map(db, dbcols, refcols)

Convert a foreign key definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns
Returns:dictionary
ForeignKey.add(obj, *args, **kwargs)

Return string to add the foreign key via ALTER TABLE

Returns:SQL statement

Unique Constraint

UniqueConstraint is derived from Constraint and represents a UNIQUE, non-primary key constraint.

class pyrseas.dbobject.constraint.UniqueConstraint(name, schema='public', description=None, **attrs)

A unique constraint definition

UniqueConstraint.to_map(db, dbcols)

Convert a unique constraint definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns
Returns:dictionary

Constraint Dictionary

Class ConstraintDict is a dictionary derived from DbObjectDict and represents the collection of constraints in a database.

class pyrseas.dbobject.constraint.ConstraintDict(dbconn=None)

The collection of table or column constraints in a database

ConstraintDict.from_map(table, inconstrs, target='', rtables=None)

Initialize the dictionary of constraints by converting the input map

Parameters:
  • table – table affected by the constraints
  • inconstrs – YAML map defining the constraints