Constraints in relational databases - Firebird

In the parlance of relational databases, any restriction imposed on the format, range, content, or dependency of a data structure is known as a constraint. Firebird provides several ways to implement constraints, including both formal, standards-defined integrity and referential constraints and user-defined CHECK constraints.

Constraints are visible to all transactions that access the database and are automatically enforced by the server. They vary in their scope of action. Some, such as NOT NULL, are applied directly to a single column (column constraints) while others, such as PRIMARY KEY and some CHECK constraints, take effect at the table level (table constraints). The FOREIGN KEY constraint has table-to-table scope.

Constraints exist “in their own right” as objects in a Firebird database. Each constraint is uniquely represented in the metadata, with the rules and dependencies of each being defined through regular relationships between the system tables.

Integrity Constraints

Integrity constraints impose rules that govern the state of acceptable data items or a relationship between the column and the table as a whole—often both. Examples are NOT NULL (rejects input that has unknown value), UNIQUE (requires that an incoming item has no matching value in that column anywhere in the table), and PRIMARY KEY (combines both of the other constraints and also “represents” the table for referential relationships with other tables).

Each of the integrity constraints is discussed individually in detail later in this chapter.

The Referential Constraint

The referential constraint is implemented as FOREIGN KEY. A foreign key constraint exists only in the context of another table and a unique key from that table, signaled implicitly or explicitly by the REFERENCES clause of its definition.

Tables that are linked in a foreign key relationship are said to be bound by a referential integrity constraint. Thus, any column or group of columns constrained by a PRIMARY KEY or UNIQUE constraint is also potentially subject to referential constraints.

Named Constraints

When declaring a table-level or a column-level constraint, you can optionally name the constraint using the CONSTRAINT clause. If you omit the CONSTRAINT clause, Firebird generates a unique system constraint name. Constraints are stored in the system table, RDB$RELATION_CONSTRAINTS.

Although naming a constraint is optional, assigning a descriptive name with the CONSTRAINT clause can make the constraint easier to find for changing or dropping, or when its name appears in a constraint violation error message. Apart from its benefits for self-documentation, this style is particularly useful for distinguishing the key definitions from the column definitions in scripts.

PRIMARY KEY and FOREIGN KEY Names

Naming a constraint has special implications for PRIMARY KEY and FOREIGN KEY constraints, particularly from Firebird 1.5 onward. It is possible to override Firebird’s “native” naming restrictions for keys.

In all versions, a supplied name will override the default name INTEG_nn and apply the supplied name to the constraint. However,

  • In version 1.5 and later, the supporting index will have the same name as the constraint.
  • In version 1.0.x, the default index name (RDB$PRIMARYnn or RDB$FOREIGNnn) is enforced.

The constraint-naming behaviors are described in more detail in the next section and in the next chapter.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics