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, table, description)

A constraint definition, such as a primary key, foreign key or unique constraint. This also covers check constraints on domains.

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, table, description, columns, expression, is_domain_check=False, inherited=False, oid=None)

A check constraint definition

static CheckConstraint.from_map(name, table, target, inobj)

Initialize a CheckConstraint instance from a YAML map

Parameters:
  • name – constraint name
  • table – table map
  • target – column (default) or domain indicator
  • inobj – YAML map of the constraint
Returns:

CheckConstraint instance

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
CheckConstraint.drop()
CheckConstraint.alter(inchk)

Generate SQL to transform an existing CHECK constraint

Parameters:inchk – a YAML map defining the new CHECK constraint
Returns:list of SQL statements

Compares the CHECK constraint to an input constraint and generates SQL statements to transform it into the one represented by the input.

Primary Key

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

class pyrseas.dbobject.constraint.PrimaryKey(name, schema, table, description, columns, access_method='btree', tablespace=None, cluster=False, inherited=False, deferrable=False, deferred=False, oid=None)

A primary key constraint definition

static PrimaryKey.from_map(name, table, inobj)

Initialize a PrimaryKey instance from a YAML map

Parameters:
  • name – key name
  • table – table map
  • inobj – YAML map of the primary key
Returns:

PrimaryKey instance

PrimaryKey.to_map(db, dbcols)

Convert a primary key definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns
Returns:dictionary
PrimaryKey.alter(inpk)

Generate SQL to transform an existing primary key

Parameters:inpk – a YAML map defining the new primary key
Returns:list of SQL statements

Compares the primary key to an input primary key and generates SQL statements to transform it into the one represented by the input.

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, table, description, columns, ref_table, ref_cols, on_update, on_delete, match, access_method='btree', tablespace=None, cluster=False, inherited=False, deferrable=False, deferred=False, oid=None)

A foreign key constraint definition

static ForeignKey.from_map(name, table, inobj)

Initialize a ForeignKey instance from a YAML map

Parameters:
  • name – key name
  • table – table map
  • inobj – YAML map of the foreign key
Returns:

ForeignKey instance

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
ForeignKey.alter(infk)

Generate SQL to transform an existing foreign key

Parameters:infk – a YAML map defining the new foreign key
Returns:list of SQL statements

Compares the foreign key to an input foreign key and generates SQL statements to transform it into the one represented by the input.

Unique Constraint

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

class pyrseas.dbobject.constraint.UniqueConstraint(name, schema, table, description, columns, access_method='btree', tablespace=None, cluster=False, inherited=False, deferrable=False, deferred=False, oid=None)

A unique constraint definition

static UniqueConstraint.from_map(name, table, inobj)

Initialize a UniqueConstraint instance from a YAML map

Parameters:
  • name – constraint name
  • table – table map
  • inobj – YAML map of the constraint
Returns:

UniqueConstraint instance

UniqueConstraint.to_map(db, dbcols)

Convert a unique constraint definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns
Returns:dictionary
UniqueConstraint.alter(inuc)

Generate SQL to transform an existing unique constraint

Parameters:inuc – a YAML map defining the new unique constraint
Returns:list of SQL statements

Compares the unique constraint to an input unique constraint and generates SQL statements to transform it into the one represented by the input.

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