Edit this page on github
Frequently asked questions on comdb2
In a database system constraints are assertions that must hold true of the data within the database. This page documents the behavior of the Comdb2 constraints engine.
The following constraint types are supported by Comdb2:
Uniqueness constraints are implemented in Comdb2 as unique keys. These constraints forbid duplicate entries from being added to the table.
In a table defined using the Schema definition language, keys are unique by default unless specified otherwise using the
dup keyword. Historically Comdb2 does not consider
NULL values as unique. That is, it would not allow multiple
NULL values in a unique key. This behaviour could be changed by adding
uniqnulls keyword (introduced in
7.0) to the key definition. With
NULL values are considered unique and, thus, be allowed multiple times in a unique key.
In standard data definition language (DDL),
UNIQUE keyword must be used to create unique keys via CREATE TABLE or CREATE INDEX commands.
NULL values are always considered unique.
Not-NULL constraint on a column ensures no
NULL values get added to the column.
A column defined in Schema definition language, by default, may not contain
NULL values unless explicitly allowed to by the
In standard data definition language (DDL), where columns are nullable by default,
NOT NULL can be added to the column definition to forbid
Foreign key constraint
Foreign key constraints ensure that a given key value in a (child) table exists in a corresponding key in another (parent) table.
In Schema definition language, foreign keys can be specified in the
Check constraints allow arbitrary expressions to be added to the table definition to constrain the values being added. Following is an example demonstrating check constraints:
testdb> CREATE TABLE grades(id INT UNIQUE, name VARCHAR(60), grade VARCHAR(2), CONSTRAINT valid_grade_check CHECK (LOWER(grade) in ('a', 'b', 'c', 'd', 'e', 'f')))$$ [CREATE TABLE grades(id INT UNIQUE, name VARCHAR(60), grade VARCHAR(2), CONSTRAINT valid_grade_check CHECK (LOWER(grade) in ('a', 'b', 'c', 'd', 'e', 'f')))] rc 0 testdb> INSERT INTO grades VALUES(1, 'foo', 'Z') [INSERT INTO grades VALUES(1, 'foo', 'Z')] failed with rc 403 CHECK constraint violation CHECK constraint failed for 'valid_grade_check' unable to add record rc = 320 testdb> INSERT INTO grades VALUES(1, 'foo', 'B') +---------------+ | rows inserted | +---------------+ | 1 | +---------------+ [INSERT INTO grades VALUES(1, 'foo', 'B')] rc 0
The following objects are not allowed in the check constraint expressions: Subqueries Parameters * Aggregate functions
When constraints are checked
Comdb2 defers constraint checks until just before a transaction is committed. This makes sense if you view constraints as assertions that must hold true for committed data. In other words, they need not hold true for uncommitted data part of the way through a transaction. There's a short example in the constraints section.
When cascade effects are applied
Comdb2 applies all cascade effects at the end of a transaction at the time at which constraints are checked. Comdb2 only tries to apply cascade effects to repair a broken foreign key constraint. This has the following implications:
If you delete a primary key which is referred to by an
on delete cascadeforeign key constraint, and then in the same transaction re-insert the same primary key value, then any records referring to that primary key value will not be cascade deleted (as the constraint holds at commit time).
If you update two primary key values to effectively swap them, and those primary keys are referred to by an
on delete cascadeforeign key constraint, then the records referring to the primary keys will not be updated as at commit time the constraints are seen to be held.