Nulls and Integrity Constraints
When you create a table with nullable columns and subsequently create integrities on those columns, the constraint must include the or...is null clause to ensure that nulls are allowed in that column.
For example, if the following define statement is issued:
define test (a=int, b=int not null)
/* "a" is nullable */
and the following integrity constraint is defined on the "test" table:
define 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 append statements fails:
append to test (b=5)
append to test (a=null, b=5)
Both of these append statements are acceptable if the integrity has not been defined on column "a". To allow nulls in column "a", you must define the integrity as
define integrity on test is a > 10 or a is null
Note: If you try to create an integrity on a nullable column without specifying the or...is null clause and the column already contains nulls, the attempt fails.
Last modified date: 08/29/2024