# Integrity Constraints - Firebird

The NOT NULL Constraint

Firebird does not support a nullable attribute, as some non-standard DBMSs do. In compliance with standards, all columns in Firebird are nullable unless explicitly constrained to be NOT NULL. The optional NOT NULL constraint is a column-level constraint that can be applied to force the user to enter a value. Null is not a value, so any attempt to input null to the column or set it to null will cause an exception.

Because of the NOT NULL constraint’s role in the formation of keys, you need to be aware of certain restrictions pertaining to it:

• It must be applied to the definition of any column that will be involved in a PRIMARY KEY or UNIQUE constraint.
• In Firebird 1.0.x, it must be applied to the definition of any column that will be involved in a UNIQUE constraint or a unique index.
• It cannot be removed from a domain or column by an ALTER DOMAIN or ALTER TABLE ALTER COLUMN statement, or by overriding a domain at column level. Do not use a NOT NULL domain to define a column that is allowed to be NULL.

The PRIMARY KEY Constraint

PRIMARY KEY is a table-level integrity constraint—a set of enforceable rules—which formally earmarks a column or group of columns as the unique identifier of each row in the table.

If you are coming to Firebird from a DBMS that uses the concept of “a primary index” to define a key (typically, file-based systems such as Paradox, Access, and MySQL), then Firebird and the world of SQL standards has a “gotcha” for you. A primary key is not an index, but a constraint in its own right. One of the rules of the constraint is that it must have an appointed unique index of one or more non-nullable elements associated with it.

Simply creating such an index does not create a primary key. Creating a primary key constraint does, however, create the required index using the columns enumerated in the constraint declaration.

A table can have only one primary key. When you define the constraint, Firebird automatically creates the required index, using a set of naming rules. The names of primary key indexes are discussed next.

While the PRIMARY KEY constraint is not itself a referential constraint, it is usually a mandatory part of any referential constraint, being potentially the object of the REFERENCES clause of a FOREIGN KEY constraint. For more details, refer to the next chapter.

Choosing a Primary Key

Identifying candidate columns to be the primary key is a science in itself and beyond the scope of this guide. Many worthy tomes have been written on the subject of normalization, the process of eliminating redundancy and repeating groups in sets of data and arriving at a correct identification of the element that uniquely represents a single row set in the table. If you are a newcomer to relational databases, the value of investing in a good book about data modeling cannot be stressed enough.

A primary key candidate, which may be one column or a group of columns, has two unbreakable requirements:

• The NOT NULL attribute must be declared for all columns in the group of one or more columns that will be used. The integrity of the key can be enforced only by comparing values, and NULL is not a value.
• The column or group of columns has to be unique—that is, it cannot occur in more than one row in the table. A driver’s license or Social Security number might be considered, for example, because they are generated by systems that are presumed not to issue duplicate numbers. To these theoretical “givens” must be added a third one:
• The total size (width) of the candidate key must be 253 bytes or less. This is not simply a matter of counting characters. The implementation limit will be reduced—in some cases, drastically—if there are multiple columns, non-binary collations, or multi-byt character sets involved.

How Real Data Can Defeat You

Using the EMPLOYEE table from the employee.fdb database in the Firebird root/examples directory (employee.gdb in the v.1.0.x kits), let’s illustrate how real data can defeat your theoretical assumptions about uniqueness. Here is a declaration that shows, initially, the meaningful data stored in this table:

This structure in fact has no candidate key. It is not possible to identify a single employee row by using (FIRST_NAME, LAST_NAME) as the key, since the combination of both elements has a medium-to-high probability of being duplicated in the organization. We could not store records for two employees named John Smith.

In order to get a key, it is necessary to invent something. That “something” is the mechanism known as a surrogate key.

Surrogate Keys

A surrogate primary key is a value of guaranteed uniqueness and no semantic content that substitutes for the key in a table structure that cannot provide a candidate key from within its own structure. The EMPLOYEE table therefore introduces EMP_NO (declared from a domain) to take this surrogate role for it:

This database also maintains a generator named EMP_NO_GEN and a Before Insert trigger named SET_EMP_NO on the EMPLOYEE table, to produce a value for this key whenever a new row is inserted. The section “Implementing Autoincrementing Keys” in

You may wish to consider the benefits of using a surrogate primary key not just in cases where the table cannot supply candidates, but also in cases where your candidate key is composite.

Composite Primary Keys

During data analysis, it sometimes happens that no single unique column can be found in the data structure. Theory suggests that the next best thing is to look for two or more columns that, when grouped together as the key, will ensure a unique row. Whenmultiple columns are conjoined to form a key, the key is called a composite key or, sometimes, a compound key.

If you come to Firebird with a cargo of background experience working with a DBMS such as Paradox, using composite keys to implement hierarchical relationships, it can be quite hard to part with the notion that you cannot live without them. Yet, in practice, composite keys should be considered with a high degree of restraint in a DBMS such as Firebird, which does not track through disk-based physical index structures to implement relationships.

Firebird does not need composite indexes and, more to the point, composite indexes do impose some problems, both for development and, when large tables are involved, for performance:

• Composite keys are typically composed of non-atomic key elements—that is, the columns selected have semantic meaning (they are “significant as data”) and are almost certainly vulnerable to external changes and typographical errors.
• Any foreign keys in other tables that reference this table will have to propagate every element of the composite key. Referential integrity is at risk from the use of non-atomic keys. A combination of non-atomic elements compounds the risk.
• Keys—foreign as well as primary—have mandatory indexes. Composite indexes have stricter size limits than single -column indexes.
• Composite indexes tend to be large. Large indexes use more database pages, causing indexed operations (sorts, joins, and comparisons) to be slower than is necessary.

Atomicity of PRIMARY KEY Columns

It is recommended practice to avoid involving in your primary and foreign keys any column that is meaningful as data. It violates one of the primary principles of relational database design, that of atomicity. The atomicity principle requires that each item of data exist completely in its own right, with a single, internal rule governing its existence.

For a primary key to be atomic, it should be beyond the reach of human decision. If a human has to spell it or type it, it is not atomic. If it is subject to any rule except the non-nullable, unique requirements, it is not atomic. Using the earlier example, even a systematic number such as a driver’s license or a Social Security number does not have the atomicity required for a primary key, because it is subject to an external system.

Syntaxes for Declaring the Primary Key

Several syntaxes are available for assigning the PRIMARY KEY constraint to a column or group of columns. All columns that are elements in a PRIMARY KEY must be previously defined as NOT NULL. Since it is not possible to add a NOT NULL constraint to a column after it has been created, it is essential to take care of this constraint before applying the additional constraint.

The PRIMARY KEY constraint can be applied in any of the following phases of metadata creation:

• In the column definition, during CREATE TABLE or ALTER TABLE, as part of the column’s definition set
• In the table definition, during CREATE TABLE or ALTER TABLE, as a separately defined table constraint

Defining PRIMARY KEY As Part of a Column Definition

In the following sequence, a non-nullable domain is defined and committed ahead, then the primary key column is defined using that domain and, simultaneously, the PRIMARY KEY constraint is applied to the table immediately:

Firebird creates a table constraint with a name like INTEG_nn and an index with a name like RDB$PRIMARYnn. (nn in each case is a number spun from a generator. The two numbers are unrelated.) You cannot influence what these names will be or change them. The effect is similar if you use the same approach when adding a column using ALTER TABLE and make it the primary key in a single clause: Defining PRIMARY KEY As a Named Constraint Another way to define the primary key in the table definition is to add the constraint declaration at the end of the column definitions. The constraint declarations are placed last because they are dependent on the existence of the columns to which they apply. This method gives you the option of naming the constraint. The following declaration names the primary key constraint as PK_ATABLE: Now, instead of the system-generated name RDB$PRIMARYnnn, Firebird stores PK_TABLE as the name of the constraint. In Firebird 1.5 and higher, it also applies the user-defined constraint name to the enforcing unique index. In this example, the index will be named PK_TABLE, whereas in other versions the index name will be INTEG_nn.

Firebird 1.5 also allows you to use non-matching, user-defined names for the constraint and its enforcing index.

Using a Custom Index

Until Firebird 1.5, it was not possible to use a descending index to enforce the primary key. From version 1.5 onward, it is possible to ask Firebird to enforce the primary key with a descending index. To do this, Firebird 1.5 introduced a syntax extension in the form of the USING clause, enabling constraint indexes to be defined as either ASC [ENDING] or DESC[ENDING] and to have a name different from that of the named constraint.

ASC and DESC determine the direction of the search order—lowest or highest first.

The following statement will create a primary key constraint named PK_ATEST and enforce it by creating a descending index named IDX_PK_ATEST:

The alternative syntax will work, too:

Adding a Primary Key to an Existing Table

The addition of table constraints can be deferred. It is a common practice for developers to define all of their tables without any table constraints and to add them subsequently, using a separate script. The rationale behind this practice is good: Large scripts notoriously fail because the author overlooked some dependency. It simply causes fewer headaches to build databases in a sequence that eliminates the time and spleen spent on patching dependency errors and rerunning scripts.

Typically, in the first script, we declare and commit the tables:

and so on.

In the next chapter, when exploring FOREIGN KEY definitions, the benefits of building databases in a dependency-safe sequence will become obvious.

CHECK Constraints

A CHECK constraint is used for validating incoming data values. It enforces a match condition or requirement that a value must meet in order for an insert or update to succeed. It cannot change the incoming value; it will return a validation exception if the input fails the check.

In a table definition, it is a table-level constraint. Unlike CHECK constraints applied to domain definitions, its VALUE element is expressed as a column reference. For example, on a domain, a CHECK clause might be

CHECK (VALUE > 10)

In a table definition, the same conditioning for a column named A Column would be expressed as

CHECK (ACOLUMN > 10)

A CHECK constraint is active in both INSERT and UPDATE operations. Although it is a table-level constraint, its scope can range from column level, through row level and, although it is not recommended, to table level and even beyond the boundaries of the table. It guarantees data integrity only when the values being verified are in the same row as the value being checked.

The search condition can

• Verify that the value entered falls within a defined range.
• Match the value with a list of allowed values.
• Compare the value with a constant, an expression, or with data values in other columns of the same row.

Restrictions

Certain restrictions apply to CHECK constraints:

• A column can have only one CHECK constraint, although its logic can be expressed as a complex search condition —one constraint, many conditions.
• A CHECK constraint on a domain-based column cannot override the inherited domain-level check. The column definition can use a regular CHECK clause to add additional constraint logic to the inherited constraint. It will be ANDed to the inherited conditions.
• A CHECK constraint cannot refer to a domain.

This is the syntax of the CHECK constraint:

The range of possibilities for defining CHECK constraints is very broad indeed—theoretically, almost any search condition will be accepted. It is important for the designer to choose conditions that are reasonable and safe, since they affect every INSERT and UPDATE operation on the table.

For example, this constraint tests the values of two columns to ensure that one is greater than the other. Although it also implies NOT NULL conditioning on both columns—the check will fail if either column is null—it does not confer NOT NULL constraint on the column:

CHECK (COL1 > COL2);

The check will fail if the arithmetic test fails or if either COL_1 or COL_2 is null. This succeeds:

INSERT INTO TABLE_1 (COL_1, COL_2) VALUES (6,5);

UNIQUE Constraints

A UNIQUE constraint, like a primary key, ensures that no two rows have the same value for a specified column or group of columns. You can have more than one UNIQUE constraint defined for a table, but it cannot be applied to the same set of columns that is used for either the PRIMARY KEY or another UNIQUE constraint.

A UNIQUE constraint, in fact, actually creates a unique key that has virtually the same powers as the primary key. It can be selected as the controlling key for a referential integrity constraint. This makes it useful for situations where you define a thin, surrogate primary key for atomicity and to improve performance of join and search operations, but you want to keep the option to form an alternative FOREIGN KEY link on the unique key for occasional use.

In Firebird 1.0.x, the NOT NULL attribute must be applied to all of the columns on which the UNIQUE constraint will operate.

Like the PRIMARY KEY constraint, UNIQUE creates its own mandatory, unique index to enforce its rules. Naming of both the constraint and the index follows the same rules of behavior applicable to other keys. The following example in isql illustrates the Firebird 1.5 naming behavior:

Remember this mantra: An index is not a key. You can define unique indexes, but making a unique index does not create a unique key. If there is a chance that you might need to use a uniquely indexed column or structure as a key, consider defining the constraint instead.