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
()¶ 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
-
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
()¶ Return string to add the CHECK constraint via ALTER TABLE
Returns: SQL statement
-
CheckConstraint.
drop
()¶ Return string to drop the constraint via ALTER TABLE
Returns: SQL statement
-
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
-
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.from_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
-
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
()¶ 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
-
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='')¶ Initialize the dictionary of constraints by converting the input map
Parameters: - table – table affected by the constraints
- inconstrs – YAML map defining the constraints
- target – column or domain indicator