Data
Data set
Column
Row
Constraints
Check constraint
- Check constraints and NULLs

Definitions

Data

Data is the quantity of facts or information input, processed and stored in a computer. Data can consist of one single entry in one field, a data set comprises a series of fields or in fact, any data quantity.

Data set

A data set is one complete data record, which is none other than a table row (which can be viewed on the IBExpert Table Editor / Data page). It encompasses a single set of information, such as, for example, one customer address or one employee record.

In a relational database the physical sequence of data sets is irrelevant.

Duplicate data sets or records (i.e. double rows) are not allowed in a relational database, as this is, in effect, storage of redundant information (see Database Normalization).

Column

A column is part of a database table, and is also known as an attribute or field. Columns list the names of the individual fields in a table.

A column describes an atomic or indivisible basic piece of information in the database, clearly differentiated from other data, e.g. zip code (and not zip code + city). Each column is assigned a certain datatype, e.g. text, numeric, date or blob. The data can also be assigned properties, such as unique, contain check constraints, autoincrements, computed values, restricted to minimum and maximum values etc. etc.

Columns are defined under the Field Definition in the Create Table dialog or Table Editor, or their definition can be based on domains. They can, of course, also be defined directly in the SQL Editor. Each defined column has the following syntax:

 ColumnName <data_type>
 DEFAULT < Default value > | NULL | USER NOT NULL
 CONSTRAINT <constraint name> <constraint def>
 COLLATE <collation sequence>;

In a relational database the physical sequence of rows and columns is irrelevant.

Row

A row is also called a tuple, record or data set. Each row represents an instance of data, belonging together, composed of different columns. It encompasses a single set of information, such as, for example, one customer address or one employee record.

In a relational database the physical sequence of rows and columns is irrelevant.

Double rows (i.e. duplicate data sets or records) are not allowed in a relational table, as this is, in effect, storage of redundant information (see Database Normalization).

Constraints

A constraint is a database examination, which ensures data consistency in the tables and among each other.

The constraint determines the range of acceptable values for a column (or columns) or data set in a database or application. This constraint can be executed automatically and so ensures that data contents are kept consistent by testing them as they are input.

A constraint can be specified for each column (or columns) in a table, to guarantee the mechanism described above. Constraints can be domain- or column-based and the specified conditions must be met when new data sets are inserted, or existing data sets are modified. They are used to verify data integrity. If a condition is not met, an exception is raised.

Firebird/InterBase^® internally generates a trigger for each check condition. Constraints can be defined as follows:

Primary Key/Unique: Specification of the unique option forces a unique entry in this column (these columns) for each data set (i.e. duplicate field entries are not allowed).

Since Firebird 1.5, in compliance with the SQL-99 standard, NULLs – even multiple – are now allowed in columns with a UNIQUE constraint. It is therefore possible to define a UNIQUE key on a column that has no NOT NULL constraint. Please refer to the Firebird 2.0 Language Reference Upate chapter, UNIQUE constraints now allow NULLs.

Foreign Key: The foreign key option determines that the column(s) is/are linked by a referential integrity relationship to the primary key of another table (i.e. the input data is only accepted if it already exists in the primary key column(s) in the referenced table).

CHECK: the check option enables each data set to be examined for validation of an expression specified in brackets. Check constraints in tables are identical to check constraints in domains.

Only one constraint is permitted per column. If the column including a constraint is based on a domain also containing a constraint, both constraints are active.

The specification of the keyword CONSTRAINT and the name are optional for all constraints. If no name is specified, Firebird/InterBase^® generates a name automatically. All constraint names are stored in a system table called DB$RELATION_CONSTRAINTS.

Since version 1.5 Firebird allows a USING INDEX subclause to be placed at the end of a primary, unique or foreign key definition. Please refer to the Firebird 2.0 Language Reference Upate chapter, USING INDEX subclause for further information.

It is only necessary to name constraints if they are to be deactivated at a later date using the ALTER TABLE DROP statement.

From InterBase^® 5 onwards, cascading referential integrity is also supported.

Check constraint

A check is a database examination, which ensures data consistency in the tables among each other. It can be executed automatically and so ensures that data contents are kept consistent by testing them before they are stored in the database.

The check constraint option enables each data set to be examined for validation of the expression in brackets following the check constraint. Check constraints in tables are identical to check constraints in domains.

A check constraint can be specified for each column in a table, to guarantee the mechanism described above. It includes an expression that must be true, so that the data set following an insert or update can be written. The field contents must be included in the permissible values, which can be specified in a list. It is also possible to test the value for a minimum and maximum value. Furthermore the value can be compared to values in other columns, in order to test dependencies.

A check constraint can only examine the values in the current data set. When simultaneously inserting or altering multiple data sets, a check constraint can only guarantee one data integrity at a time at data set level.

If other data sets are referenced in the check, these could have been modified by another user at the time of entry, and therefore possibly have become invalid, even though the check constraint's test approved the data set. At the time of a check constraint validation, other data is only read for the check. For this reason, the values for the current operating sequence remain constant, even if another user has modified one of the values already referenced for validation.

A check constraint can be created directly when creating a table. When creating a check constraint, the following criteria should be taken into consideration:

A check constraint cannot reference a domain.
A table column can only contain one check constraint.
A check constraint defined by a domain cannot be overridden by a local check constraint. However additional constraints can be specified.

In a check definition the VALUE keyword represents the value of the respective table column. The value examination is generally performed when inserting or updating this table column. The Check Value options permit diverse operations (please refer to Comparison Operators for a full list of possible operators).

Referential integrity declarations and primary key definitions are special check constraint compositions.

Only one constraint is permitted per column. If the column is based on a domain containing a constraint, both check constraints are active.

It is only necessary to name constraints, if they are to be deactivated at a later date using the ALTER TABLE DROP statement.

Please note that if you want to change the CHECK constraint for a domain that already has a constraint defined, the existing constraint must first be dropped and then the new one added. ADD CHECK does not replace the current constraint with the new one. It is also important to realize that altering a CHECK constraint does not cause existing database rows to be revalidated; CHECK constraints are only validated when an INSERT or UPDATE is performed. One way of overcoming this limitation is to perform an UPDATE query using a dummy operation. If existing rows violate the new CHECK constraint, the query fails. These rows can then be extracted by performing a SELECT.

Check constraints and `NULL`s

If a CHECK constraint resolves to NULL, Firebird versions before 2.0 reject the input. Following the SQL standard to the letter, Firebird 2.0 and above let NULLs pass and only consider the check failed if the outcome is false.

This change may cause existing databases to behave differently when migrated to Firebird 2.0+. Carefully examine your CREATE/ALTER TABLE statements and add and XXX is not null predicates to your CHECKs if they should continue to reject NULL input.

Please refer to the Firebird 2.0 Language Reference Upate chapter, CHECK accepts NULL outcome for further information.