Declare referential integrity property

Official Content
This documentation is valid for:
Controls the DBMS referential integrity declaration.

Values

No GeneXus will not declare integrity references for the database nor eliminate those which are defined during the creation/reorganization process.
Remove GeneXus will remove, during the creation/reorganization process, all integrity references related to the table/s being reorganized.
Yes GeneXus will declare all integrity references for the database during the creation/reorganization process (default value since GeneXus X version).

Description

To define this database property, the property must be configured in the ‘Creation/Reorganization Information’ section within the model’s ‘DBMS Options’. This property allows registering in the DBMS when the database is created that Referential Integrity will be enforced. During reorganizations, the referential integrity declaration is done in an incremental way. It means that the only tables being modified in the reorganization will be taken into account to set its referential integrity. Therefore, if the reorganization attempts to violate the Referential Integrity defined in the DBMS, an error message will be triggered.

Notes

  • When this option is set to No, GeneXus will NOT generate referential integrity for the DBMS. However, this option will not affect referential integrity controls performed by the programs generated by GeneXus.
  • When this option is set to Yes, the reorganization process may block some other tables in addition to the ones specified in the Impact Analysis Report. When a table is being reorganized, all integrity references which make use of this table as the "parent" (superordinated) table must be temporarily eliminated.You may have problems if you set this option to Yes for an existing database because the data may not validate the integrity references which are being declared. In this case, the DBMS detects the inconsistencies during the reorganization process and returns an error code.
  • Changing this option setting will gradually be reflected on the database as the tables are reorganized.
  • Integrity references between local and remote tables cannot be defined.
  • When this option is set to Yes, the only possible values the Primary Key Definition property can be set to are: Primary Key and Constraint; the Index value is not allowed.
  • If you want to eliminate the existing Referential Integrity, the Remove value must be used. The database must be created again, and then the Referential Integrity must be changed to No.
  • In a Client/Server model with local tables, the referential integrity is not defined; thus, it is possible to perform actions on a local table which violate the integrity of the server table and vice versa.
  • If you are working with the “Generate Null for Nullvalue” property in NO (the generated value is the GeneXus Null one and not the DBMS Null one) the Allownulls rule on a foreign key produces an error, since the GeneXus null value violates the DBMS referential integrity because it will search this value as key in some table. If you have the “Generate Null for Nullvalue” property in YES there are no problems.
  • In a Client/Server model, the rule Error_handler can be used to handle the error, thus preventing the end of the program.
  • If the error is not handled, the program will cancel because of the Referential Integrity violation.

Samples

Let’s suppose there is an application with the following structures (with Referential Integrity):

Table icon   Category
       CategoryId*
       CategoryName

Table icon  Client
      CliIentd*
      ClientName
      ClientTelephone
      CategoryId
      CategoryName

Let’s consider both tables with data.

There is also a process called Delete, which eliminates all the records of the table Category: 

For each Defined by CategoryName
    Delete
Endfor

This process tries to delete records of a table that are in another table as foreign key, thus violating the referential integrity. Depending on the DBMS, or if Access is used, an error will occur. By way of example, the following dialog appears in SQL Server:

Error code 547 : Delete statement conflicted with COLUMN REFERENCE constraint …

This causes the program to cancel.

The Error_handler rule can be used in this case to handle the error, thus preventing the program from closing.

The following steps should be taken:

Define a sub-routine for the procedure in order to handle the error:

Sub 'Error_handler'
    If &gxDBErr= 547 //SQL Server error code
        Msg('This Procedure can not be carried out because the referential integrity is violated.')
        Rollback
    EndIf
EndSub  // 'Error-handler'

In addition to that, define the rule: Error_handler(‘Message’).

Scope

Available at the datastore.
Datastore: Iseries, DB2 UDB, INFORMIX, MYSQL, ORACLE, POSTGRESQL, SQLSERVER
Platforms: Web(.Net, Java)

See Also

Error_Handler Rule
Generate null for nullvalue() property