Advanced schema (database table) properties

The Omnis Studio schema clesses define the structure of the database tables.

In addition to the Omnis Studio properties for the schema columns:

  • Name
  • Description
  • Primary key
  • Type
  • Subtypes
  • Null or no null

Crossmodel includes an ample set of properties that can create and maintainthe database in a more advanced way.

ID (sequence) column

The CrossModel schemas can have a ID integer column, that will be autoincremented and never changed after the record is inserted and is being used for foreign keys. In this way the explicit search key (code) can be changed at any time without affecting connections.

For example,a stock item can have the ID column and the item code (search key). In this way the value of the stock item code can be changed at any time, since it is not repeated in any other table.

Search key

This is the key used by the user to search the record.

Since it is defined in the schema definition, it can be used to manage table connections without writing code. The program will know that that field that is being edited is the search key for a table, and can act accordingly without the need to write any code.

Indexes

Each column of the schema can be defined as indexed, unique or not unique.

In addition to that, complex multicolumn indexes can be defined.

Null and not null

The Omnis Studio property “not null” is treated in a different way in the insert-update stored procedures.

While inserting a NULL value in a NOT NULL columns generates an error, in the insert-update stored procedures the NOT NULL columns are converted to 0 is numeric, and to ” (empty) if character.

Advanced create tables statements generation

The structure of the table that is created with the $createnames can be customized by the programmer.

Table connections

The schemas, and the tables in the database, are connected to other tables, and create foreign keys in the database.

The table connections are described in the section Connections, foreign keys and referential integrity