Connections, foreign keys and referential integrity

One of the most important features to meet tha ACID requirements of modern database is the possibility to create connections between tables with foreign keys, to ensure data integrity.

CrossModel can create and mantain connections between schemas (tables of the database), allowing full use of referential integrity of the databases.

One of the most important features of CrossModel is the possibility to create connections between schemas, and therefore tables of the database.

The connection is created simply with drag and drop of an unique index column from the parent table into the child one. If a column with the same name is already present, it will be used as foreign key, otherwise a new one will be added. The user can anyway change the name of the foreign key, or force the use of any existing column.

Referential integrity

The connection creates by default a foreign key in the database, but it can be forced not to do so.

The foreign key on delete can perform CASCADE, SET NULL or nothing, and in this case the parent cannot be deleted.

Foreign keys tree

If the user tries to delete a record that cannot be deleted because of a foreign key, he gets a message like this:

“[SQL Server]The DELETE statement conflicted with the REFERENCE constraint “my_foreign_key”. The conflict occurred in database “my_database”, table “dbo.may table”, column ‘my_column’

Furthermore, the error is reported only for the first foreign key; if the data is modified to avoid this problem, there can still be other foreign keys to block the delete action.

In CrossModel, the foreign keys create a foreign keys tree, in which all the tables to be tested are included, also the indirect ones. In fact, if a table ha children with ON UPDATE CASCADE, ven the children of the child table have to be tested.

CrossModel, before any delete, checks all the foreign keys tree at any level for connections that block the delete action, and if so a message is shown, with the list of the the tables that are blocking the delete, with the description of every table and the number of records that are connected.

 

Use of tables connections

The CrossModel data modeler has the big advantage of being fully integrated into the applications, so the table connections are not only used to create foreign keys in the database, but also  in many other situations:

  • Creation of smart queries
  • Automatic actions in enter data mode
    • Selection from list of records
    • Insert new record in parent table
    • Detail of parent record