RDB$NULL_FLAG is null for all columns

Mason_Wheeler_32914 0
Viewed 141

I'm trying to run a query to extract metadata of all columns in Firebird. Here's what it looks like:

select
      f.RDB$RELATION_NAME as tableName,
      f.RDB$FIELD_NAME name,
      fd.RDB$FIELD_TYPE type,
      fd.RDB$FIELD_SCALE scale,
      fd.RDB$FIELD_SUB_TYPE subtype,
      fd.RDB$DIMENSIONS dimensions,
      fd.RDB$NULL_FLAG notNull,
      fd.RDB$FIELD_PRECISION precision
from rdb$relation_fields f
join rdb$relations r on f.RDB$RELATION_NAME = r. RDB$RELATION_NAME
join RDB$FIELDS fd on fd.RDB$FIELD_NAME = f.RDB$FIELD_SOURCE
where f.RDB$UPDATE_FLAG = 1
  and r.RDB$RELATION_TYPE = 0
  and coalesce(r.RDB$SYSTEM_FLAG, 0) = 0

For the most part, it works fine. But for whatever reason, every single result gives null in the fd.RDB$NULL_FLAG column. According to the documentation, this value should be null if a column is nullable, or 1 if it is not. (It should go without saying that I do, in fact, have some NOT NULL columns in my database!)

Any idea what's going wrong, and how I can get correct reporting of the nullability status of my columns?

firebird sql
2 Answers

To expand on the answer by user13964273, a field of a table is defined in two parts: an entry in RDB$RELATION_FIELDS for the field itself, and a domain definition in RDB$FIELDS. If a field is defined with a basic type instead of a (named) domain, an unnamed (numbered) domain is generated.

This unnamed domain does not have RDB$NULL_FLAG set, this is equivalent to CREATE DOMAIN <domainname> <basicdatatype>, which also does not set the RDB$NULL_FLAG in RDB$FIELDS. Instead, the NOT NULL constraint is recorded in RDB$RELATION_FIELDS.RDB$NULL_FLAG.

The RDB$FIELDS.RDB$NULL_FLAG is only populated with 1 when creating an explicit domain with a NOT NULL constraint, for example:

create domain int_not_null integer not null;

In short, you need to use - for example - COALESCE(f.RDB$NULL_FLAG, fd.RDB$NULL_FLAG, 0) to determine the nullability of the column, where 0 means nullable and 1 means not-nullable.

Similar checking of values in both tables also applies to a few other columns that exist in both RDB$FIELDS and RDB$RELATION_FIELDS: RDB$DEFAULT_VALUE/RDB$DEFAULT_SOURCE and RDB$COLLATION_ID.

user_4668620

You should also check f.RDB$NULL_FLAG which overrides domain definition.

user_139642730
Related