Nulls and Integrity Constraints
When creating a table with nullable columns and subsequently creating integrities on those columns (using the CREATE INTEGRITY statement), the constraint must include the OR...IS NULL clause to ensure that nulls are allowed in that column.
For example, if the following CREATE TABLE statement is issued:
CREATE TABLE test (a INT, b INT NOT NULL);
/* "a" is nullable */
and the following integrity constraint is defined on the test table:
CREATE INTEGRITY ON test IS a > 10;
the comparison, a >10, is not true whenever a is null. For this reason, the table does not allow nulls in column a, even though the column is defined as a nullable column.
Similarly, the following INSERT statements fail:
INSERT INTO test (b) VALUES (5);
INSERT INTO test VALUES (null, 5);
Both of these INSERT statements are acceptable if the integrity had not been defined on column a. To allow nulls in column a, define the integrity as:
CREATE INTEGRITY ON test IS a > 10 OR a IS NULL;
Note: If an integrity on a nullable column is created without specifying the OR...IS NULL clause and the column contains nulls, the DBMS Server issues an error and the integrity is not created.
Last modified date: 04/03/2024