SQL::Schema::Constraint - A constraint of a database table


SQL::Schema::Constraint - A constraint of a database table


  my $constraint = SQL::Schema::Constraint->new(%attr);
  my $sql = $constraint->constraint_clause;
  print $sql;
  print "$constraint";


SQL::Schema::Constraint is a class for objects representing a database table's constraint. The methods of an instanciated object do allow to access the information within a database's data dictionary and to represent them as SQL create statements and the like using the proper SQL dialect.


  $constraint = SQL::Schema::Constraint->new(%attr);

The new method instanciates a constraint object. The object is an in memory representation of a (possible) database table's constraint. The attributes are given as key value pairs by the hash %attr. Possible keys are:

  key                required?   value description
  constraint_name    yes         the constraint's name
  constraint_type    yes         one of
                                   C for check
                                   P for primary key
                                   U for unique key
                                   R for referential integrity
  search_condition   no          text of search condition for
                                 table check
  delete_rule        (*)         either `cascade' or `no action'
                                 default: `no action'
  status             no          either `enabled' or `disabled'
                                 default: `enabled'
  deferrable         no          either `deferrable' or `not deferrable'
                                 default: `not deferrable'
  deferred           no          either `immediate' or `deferred'
                                 default: `immediate'
  generated          no          either `generated name' or `user name'
                                 default: `user name'
  validated          no          either `validated' or `not validated'
                                 default: `validated'

These keys (except columns) and their possible values correspond exactly to the data dictionary view user_constraints and are described within Oracle's Server Reference.

Additionally the following keys are possible/required. They do not relate to columns within the view user_constraints.

  key                required?   value description
  columns            no          a reference to a list with
                                 column objects
  r_schema           (*)         the referenced table's schema
  r_table_name       (*)         the referenced table's name
  r_columns          (*)         a reference to a list containing
                                 column objects representing the
                                 referenced columns
  (*) means: required for referential constrains otherwise not
  @columns = SQL::Schema::Constraint->select_columns($dbh,$schema,$name);

Returns a list of columns in the correct sequence. All the columns belonging to the constraint with the name $schema.$name are selected from the database and returned. All the elements of the returned list are objects of the class SQL::Schema::Table::Column.

The method's arguments are as follows:

A database handle as defined by DBI(3).

The name of the constraint's schema.

The constraint's name.

  $constraint = SQL::Schema::Constraint->select($dbh,$constraint_name);

The select method fetches the attributes required by new from the database and returns the constraint object. (It calls new internally.)

If the constraint could not be found within the database, the method returns undef.

The method's arguments are as follows:

A database handle as defined by DBI(3).

The name of the constraint.


The following attribute methods do return the current value of the attributes (as handed over to the new method):

  $constraint_name   = $constraint->name;
  $constraint_type   = $constraint->type;
  $search_condition  = $constraint->search_condition;
  $delete_rule       = $constraint->delet_rule;
  $status            = $constraint->status;
  $deferrable        = $constraint->deferrable;
  $deferred          = $constraint->deferred;
  $generated         = $constraint->generated;
  $validated         = $constraint->validated;
  @columns           = $constraint->columns;
  $r_schema          = $constraint->r_schema;
  $r_table_name      = $constraint->r_table_name;
  @r_columns         = $constraint->r_columns;

The return value of many of the above extra values is text with just two possible values. Sometimes it is more comfortable for programmers to have methods returning boolean values. The table below names extra methods based on the methods above. They return 1 if the base method returns the value shown within the 3rd column. Otherwise the return 0.

  extra method     base method    1 if eq          0 if eq
  cascade          delete_rule   cascade          no action
  enabled          status        enabled          disabled
  deferrable_b     deferrable    deferrable       not deferrable
  deferred_b       deferred      deferred         immediate
  generated_b      generated     generated name   user name
  validated_b      validated     validated        not validated


  $bool = $constraint->cascade;
  $bool = $constraint->enabled;
  $bool = $constraint->deferrable_b;
  $bool = $constraint->deferred_b;
  $bool = $constraint->generated_b;
  $bool = $constraint->validated_b;
  $sql = $constraint->constraint_clause;
  $sql = "$constraint";

Returns a string containing the constraint clause which could be used as part of an SQL statements for creation of the corresponding constraint. This method is overloaded with the string operator. So the two examples above are equivalent.


  SQL::Schema::Constraint is Copyright (C) 2000,
    Torsten Hentschel
    Windmuehlenweg 47
    44141 Dortmund
    Email: todd@bayleys.ping.de
  All rights reserved.
  You may distribute this package under the terms of either the GNU
  General Public License or the Artistic License, as specified in the
  Perl README file.


DBI(3), the SQL::Schema(3) manpage, the SQL::Schema::Table(3) manpage

 SQL::Schema::Constraint - A constraint of a database table