Generating relation into SQL databases

Classic Servoy
1

Servoy is enabling to define relationship at logical level; there are three types of relation definable:

  1. Real 1-N relation
  2. Usage N-1 of relation defined at #1
  3. Logical relations involving variables

I’ve found no way to generate from Servoy repository directly into SQL backends “real 1-N relations” (#1) to enforce referential integrity.

Is there any way?

Tnks,
Gianni

2

Ciao Gianni,

if I understand you right, you want to add a constraint, such as a foreign key constraint, on the SQL database from Servoy. There is no way of doing this in version 2.2, but you can add a constraint directly in your SQL database using some SQL tool.

The only problem is that when the user attempts to violate the constraint, you have no way of trapping the error message from the database in Servoy – it just displays in a dialog window. Two ways around this,

  1. you manually check that the contraint will not be violated in your Servoy methods (I know, extra coding :frowning: )
  2. you write a trigger which raises a better error message when the user attempts to violate constraint. (This works with PostgreSQL at least, haven’t tested other databases)
3

Note that as of the next 3b4 build you will have some interesting trapping options in servoy too. More on that later

4

Tnks Christian and admin for your replies!

The best solution for now IMHO is to write coding in Servoy app to force referential integrity.

However it would be nice if Servoy automate it; the functionality is quite simple:

A) Servoy developer knows from relations type#1 defined into the repository which is the costraint to implement.
B) If target DBMS is supporting costraint Servoy generate SQL to drop such costraint into DBMS engine.
C1) At runtime if BOTH TABLES are on the same data provider AND the underlyng DBMS is supporting costraints Servoy trap the error from DBMS and return an error signal to the application
C2) At runtime if TABLES are on different data providers or the underlying DBMS is not supporting constraint Servoy raise a standard SQL where PrimaryKeyIWantToDrop is used in ForeignKeyReferred. If it is used return to the application the same error as in C1

Point A and B are obviously depending from the types of costraints Servoy decide to support; but the most valueable ones are DELETE CONSTRAINTS with RESTRICTED, CASCADE and NULLIFY scopes.

This implementation could save MANY hours of development in every application!

Tnks again for your replies!

Ciao,
Gianni