The null value for a given attribute should be considered as "not specified" or "not available" or "not assigned". It is different from the "empty" value that is a special value (i.e. zero for numeric data, empty string for character data, etc.). As from GeneXus 9.0, you can specify, in a Transaction's structure, whether or not an attribute can have a null value. This information is useful for several purposes:
- Protect data
- Improve the referential integrity controls
- Enable better join performance
For detailed information about how you can handle, or avoid, null values in GeneXus click here .
The Transaction's Nulls column can take the following values:
- No: means that the attribute in the underlying Table does not allow the null value.
- Yes: means that the attribute in the underlying table allows the null value.
- Compatible: This is a special, compatibility value, that is available only in Knowledge Bases from previous GeneXus versions. See Compatible Section for more details.
The default value is No.
The Nulls column in the Transaction's structure allows you to set whether or not an attribute should allow the Null value. It can be set for any attribute that is stored in the underlying table except for the primary key attributes (that do not support the null value by definition).
Parallel Transactions are Transactions having the same Primary Key composition in a given level. If there are Parallel Transactions in a Knowledge Base, null definitions are shared among them. This means that the last Parallel Transaction saved from a set, overwrites previous null definitions for all the set.
Attributes whose null property can be changed
The Null column is enabled for those attributes that are stored in the underlying table except for those comprising the primary key that, by definition, cannot have Nullable attributes.
Why should you care about Nulls?
Nulls are very important in the relational model. Allowing the null value for a given attribute means that it may, under certain circumstances, be "ignored" as its value is "not set". On the other hand, if an attribute does not allow the null value, a valid value must always be assigned to it. Nulls information is used in GeneXus for defining Data Base tables and referential integrity controls, to determine the Join Type used to navigate de Data Base and check program's logic.
Data Base tables
Nulls definition is used by GeneXus when it creates KB tables. The IAR (Impact Analysis Report) shows Nulls capabilities of every attribute on each KB table. Changing Nulls definition forces a data base reorganization.
Tables in a KB are related to each other. References are made from one to another through foreign keys to primary keys. Null definitions for attributes comprising a foreign key let Genexus know how "strong" these references are. Say for example that none of the attributes comprising a foreign key allow the null value. This is a strong reference (also known as a not null reference): it states that, no matter what happen, the foreign key must always point to an existing Primary Key value of the referenced table. On the other hand, a foreign key having at least one attribute supporting the null value is a weak reference (also know as null reference) and states that if this (or these) attribute(s) are null (any of them if many) the reference should not be checked.
When a foreign key is a compound key (has more than one attribute) and nulls are allowed for any of its attributes, new references may be defined if the remaining attributes comprise a foreign key too as in the following example.
In the CLIENT Transaction structure, the CountryId and CityId attributes make up a compound foreign key for the CITY table. If null values are not allowed for either CountryId or CityId, the reference's existence is unconditionally verified. On the other hand, if null values are allowed for CityId, reference to table CITY will be checked only if CityId is not null and a reference to table COUNTRY needs to be created. This last reference is needed to avoid entering an invalid (non existing) CountryId when CityId is null.
Determining Join Type
Join refers to how tables are "linked" to retrieve data to fulfill a request (For eachs, either explicit or implied). At the time of this writing Genexus supports two types of Joins: Natural and Outer. Natural means the if, for example, two tables are joined, records for not matching join conditions should not be retrieved. Outer means that records not matching join conditions must be retrieved.
Based on Null definitions for foreign keys, Genexus knows when to use Natural (not null reference) or Outer (nullable references).
Checking program logic
Null definitions can be used to check program logic. Basically, to avoid setting the null value for attributes that do not allow it.
Nulls in navigation reports
Navigation reports do not normally show information about null definitions. They do show, however, the effects of null definitions, for example:
- A '~' symbol is prefixed to the table icon when nulls are allowed (Outer Join)
- The 'allowing nulls' suffix is added to 'READ <TABLE>' in the Detailed Navigation Report.
- An '=' symbol is prefixed to the table icon when nulls are not allowed (Natural Join).
Nulls and Foreign Keys Summary
- If a foreign key has the Null property set to 'No' for all its attributes:
- Not null references will be allowed when inserting or updating.
- Joins referencing these tables will be generated as Natural Joins.
- If a foreign key has some of its Compose attribute set to Yes:
- Null values will be allowed when inserting or updating.
- New referential integrity control is performed if other attributes of this FK match with another Primary Key.
- Joins referencing these tables will be generated as Outer Joins.
- If a program (Transaction or Procedure) inserts a record in a table with no referenced attributes with null value property in Yes, and the Initialize Not Referenced Attributes property is set to No, the following error message appears: spc0081: Attributes %1 do not allow nulls in table %2 and are not referenced %3.
To fix the error:
- If the program is a Transaction, you may reference these attributes in the transaction structure or disable insertion in the appropriate level. Use the 'error() if insert' rule.
- If it is a Procedure, the 'New' group must reference them. Otherwise, you may change the nullability property of these attributes.
- Set the Initialize not referenced attributes property to Yes.
- If the Nullvalue() function is assigned to an attribute that doesn't support null values, and the Generate Nulls for Nullvalue() property is set to Yes, the following error occurs: spc0082: Attribute %1 in table %2 does not allow nulls.
To fix the error:
- Remove the assignment and change the Generate Null for Nullvalue() property value, or
- Change the attribute nullability for this table.
- If a foreign key doesn't allow nulls and the AllowNulls rule is specified for it, the following message appears: spc0085: AllowNulls rule conflict with null definition for foreign key %1.
To fix the error:
- Remove the AllowNulls rule (it should not be necessary) and change the nullability of foreign key attributes as needed.
When changing an attribute Null=Yes to Null=No or vice versa, a reorganization takes place. If the modified attribute belongs to a foreign key, this reorganization can change the foreign key constraint definition. If the attribute doesn't belong to a foreign key:
- Changing from Null=No to Null=Yes doesn't present problems because a new value is added (the null one) and the existent data cannot have it.
- Changing from Null=Yes to Null=No deletes a possible value of the existent data. To be able to reorganize the data without any errors in the process, you have to replace the null values with one of two possible values: the one specified in the Initial Value property or, in case this value has not been specified, the empty value corresponding to the attribute data type. In this case, the following warning appears: Rgz0008: Attribute %1 changed from AllowNulls=Yes to AllowNulls=No and no initial value was specified. Null values will be treated as empty ones.
Besides, if the attribute belongs to a foreign key, changes in the foreign key constraint definition could cause the creation of Load Referential Integrity programs.
If a new attribute is defined in a existing table it doesn't accept null, the following warning is displayed during reorganization: Rgz0007: Attribute %1 does not allow nulls and doesn't have a default value. An empty default value will be used. If this new attribute is a Foreign Key also a new referential integrity constraint is created. As consequence, if the default value assigned to this attribute does not exist in the referenced table, the reorganization will fail. This is noticed by the following message: Reorganization may fail if table %1 has records and the key value %2 does not exist in table %3.
Previous versions of GeneXus did not allow defining attribute nullability. For this reason, when opening a Knowledge Base that was created with a previous version with the 9.0 version, those attributes that can be defined as null (foreign keys and secondary attributes) will have the Compatible value in this property. In this way, compatibility with previous versions is kept.
This means that all the attributes that have the Compatible value will be able to support the null value if the DBMS supports nulls. Transactions will perform strict integrity controls on foreign keys unless the corresponding AllowNulls rule is specified. The Join type to be used in the rest of the objects will be determined by the Join Type property.
Nulls Behavior Model Property
The Compatible value for the Null attributes property will be displayed only if the Nulls Behavior Design Model property has the Version 8.0 and prior (Deprecated) value. This is the value set if the Knowledge Base comes from a previous version. The default value for new KBs is Current Version, where the Compatible value will not appear.
For Knowledge Bases that were created with previous GeneXus versions, we recommend changing the Compatible value to one of the Yes/No values in a gradual migration process.
During this process, some foreign keys may remain with mixed Compatible/Yes/No values. In these cases, the expected behavior is as follows:
- When combining the Compatible value with the Yes or No values in attributes that make up a foreign key, the Compatible value is considered as Yes.
IMPORTANT: Only changing the Nulls Behavior property to the Current value will not guarantee backwards compatibility.
Distribution and Consolidation
Interaction between KBs with one Nulls Behavior value or the other (including interaction with KB of previous versions):
What is imported from a KB with the Current version value will contain attribute nullity information according to what was specified. When importing with GeneXus 9.0 or later, the nullity values specified in the export are always taken without further considerations. When importing with GeneXus 8.0 or previous versions, some type of warning should appear, but the import is allowed.
What is exported from a KB with the Version 8.0 or Prior value will contain the nullity information only for those attributes with a value other than Compatible. When importing with GeneXus 9.0 or later to a KB with the Current version value, nullity values will be recalculated. They will all become No and compatibility will not be guaranteed.
When importing with GeneXus 9.0 or later to a KB with Version 8.0 or Prior value, the values specified in the Export will be taken and Compatible will be assigned to those without an assigned value.
Nulls vs. Allownulls Rule
Specifying the Allownulls rule for a group of attributes that make up a foreign key is equivalent to specifying that any or all these attributes may be null in the structure. However, there are some differences with the Null property:
- The rule is local to the object while the nullity definition is global to the model. Remember that you specified that attribute X in table Y may be null.
- On specifying that an attribute that is part of a compound foreign key may be null, additional integrity controls, which are not added with the Allownulls rule, could be added.
The Allownulls rule may only be used for foreign key attributes with Compatible Null value.
This rule is considered as "deprecated".
- The Join Type model property will never be considered when there aren't attributes with Compatible Null values. As a consequence, the property will not be shown when the Null Behavior property takes the Current Version value.
Note: If you have a transaction, such as Invoice, that allows null values in its foreign key attribute (ClientCode), all queries that you make involving both tables will be joined with an Outer Join. To make a query that avoids these null references, you may add this condition: ClientCode = nullvalue( ClientCode)
Defined By InvoiceDate
Where ClientCode = nullvalue(ClientCode)
- Non-SQL generators (Cobol, RPG, VB-Access, VFP-DBFs) will continue to create tables and generate outer joins as in previous versions. The only feature that they take into account is new referential integrity controls added in Transactions when there are compound foreign keys allowing null values.
- If the reorganization generator is RPG or COBOL, secondary generators will not take into account the Null Property information for the code generation, due to this generator do not support nulls.
- As it mentions above, when a foreign key is a compound key (has more than one attribute) and nulls are allowed for any of its attributes, new references may be defined if the remaining attributes comprise a foreign key too. Referential integrity control on delete in the referenced table has not implemented yet.
Objects: All objects that could update the Data Base.
Language: All (mainly SQL Generators).