Table properties

You can set several properties for tables using the Inspector palette or, for certain ones, using the context menu for the tables.

A trigger is a method that runs automatically when certain events related to the table occur. The events are:

• On saving new record,
• On saving an existing record,
• On deleting a record.

Trigger events can be set in the Inspector palette or using the context menu associated with the table (right-click on the title area of the table):

Check each event for which you want to activate the trigger. 

You can access the trigger of a table directly from the Structure editor by clicking on the Edit... button in the Inspector palette or by selecting Edit Method... in the context submenu. For more information about triggers, refer to Types of Methods.

Notes:

• You can also create and display triggers with the Methods Page of the Explorer.
• The information bar of the Structure editor indicates the triggers that are activated for each table.

The trigger that you enter in the Method editor will only be executed when the selected event(s) are detected.

You can set different attributes to configure the appearance and general behavior of each table. 

This option lets you make a table invisible in the Application mode and for plug-ins. You can activate this option for utility tables or tables that are not being used temporarily.

Making a table invisible allows you to limit the operations that a user can perform on a table by making the table and its fields invisible in all editors and some dialog boxes that appear in the Application environment. It can also not be used by plug-ins. 

The editors and dialog boxes concerned in the Application environment include the following:

• All query editors (see Searching records),
• Order by editor,
• Label editor,
• Quick report editor,
• Exporting and importing data dialog boxes,
• Formula editor.

In each of these editors, the user is unable to see or choose the table or any of its fields. For instance, the user cannot include any fields from an invisible table in a report or label.

Note: When using these editors, users have the option of saving their specifications (e.g., the query or sort they created) to disk files. In this case, any specified tables or fields that are subsequently made invisible will may still be used in the operation. In addition, users can type the names of invisible tables and fields in the dialog box.

Invisible tables and fields are displayed in italics in the Structure editor window.

A color can be attributed to each table. Using colors helps to organize the structure of a large database. For example, you could use one color for all tables that relate to customers and customer records and another color for tables that relate to inventory and inventory records.

Note: It is also possible to attribute a color to each field individually (see ) as well as to each relation (see Definition).

To set the color of one or more tables, make your selection and then choose the color using:

• The Color button of the Structure editor tool bar,
• The Color command in the context menu of the tables,
• The Color option in the Inspector palette.

The Automatic option can be used to apply the standard original color to the table.

This option lets you optimize the deletion of a selection of records made using the DELETE SELECTION command.

When 4D deletes a selection, the record markers are also deleted. A record marker is a header attached to the record that contains information relating to this record. Deleting both markers and records is slower than deleting only records. In certain cases, it may be desirable to not automatically delete the record markers. 

This option lets you set the type of deletion desired. To accelerate the deletion of a large selection made using the DELETE SELECTION command, deselect the Records definitively deleted option. Record markers will then not be deleted. This option cannot be set by programming.

When this option is checked, 4D generates the information necessary for replicating the records of the table (based more particularly on the primary key of the table). The record replication function allows data to be synchronized between two or more 4D databases for better security.

Once the option is activated, the replication mechanisms must be implemented using specific SQL language commands in 4D or using the HTTP protocol. For more information about this option and about the SQL replication mechanism itself, refer to Replication via SQL. For more information about replication using HTTP, refer to URL 4DSYNC/.

By default, this option is not checked. For this option to be available, you must specify a primary key for the table to be replicated. Otherwise, the option is dimmed. 4D lets you set a primary key for a table directly in the Structure editor (see below).

This option controls whether the table is exposed in the context of 4D Mobiles requests sent to the 4D database via REST. By default, all tables are exposed in 4D Mobile.
For security reasons, you may want to only expose certain tables of your database to 4D Mobile calls. For instance, if you created a [Users] table storing user names and passwords, it would be better not to expose it.

If you do not want to expose a table (nor any of the fields it contains), uncheck the Expose with 4D Mobile Service option for the table.

Note: You can also set this option at the level of each field of the table, see Field properties.

This option can be used in the context of the "4D Mobile" functionality. For more information, refer to the 4D Mobile manual.

By default, this option is checked for all new tables created and for all tables in converted databases.

Check this option in order for operations performed on the table’s data to be included in the database log file (when it is generated). This option must generally be checked for most tables. However, for optimization purposes, you can uncheck it, for example for temporary tables or tables used for importing data.

Note: This option is grayed out when the table does not have a primary key.

It is important to note that this option only indicates that the table’s data must be journaled if the database uses a log file; it does not enable the journaling procedure itself at the database level (see Managing the log file).

4D displays a warning icon to the right of the Include in Log File option when the required conditions are not met.

As long as this icon is shown, journaling is not yet enabled. You can place your mouse over this icon to find out the cause for the warning:

The following messages may be shown:

The Comments area of the Inspector palette lets you store additional information about the table. These comments are available for all the developers.
Note that each field and each relation has its own comments area.

The SQL area of the Inspector palette includes the “Schemas” menu and an information area.

• The “Schemas” menu lists all the SQL schemas specified in the database. You can use this menu to set the schema to which the table will belong (it is also possible to modify this property via SQL commands). Every database has at least one schema, named DEFAULT_SCHEMA. By default, all the tables belong to this schema. For more information about SQL schemas, refer to Schemas in the 4D SQL Reference guide.
• The information area indicates whether the name of the table respects the rules regarding SQL nomenclature (for example, unlike 4D, SQL does not allow a field name to contain spaces).

A primary key designates the field(s) used for uniquely identifying the records in a table. Setting a primary key is necessary for the record replication function in a 4D table (see Replication via SQL) as well as for the journaling function (see Managing the log file).

You create, edit and/or remove primary keys directly using the context menu of the Structure editor.

Note: Primary keys can also be set using the SQL language by means of the PRIMARY KEY clause followed by the list of columns (see Primary key in the 4D SQL Reference Guide).

Primary key fields ​​should be handled with care to ensure data integrity at all times. In particular, a primary key fields must respect the following rules:

• it must not be empty,
• it must be unique,
• once created, it should (in principle) never be modified, especially if:
  
  • 4D replication or synchronization features are enabled
  • 4D Mobile (or similar) feature is enabled
  • the database logging function is enabled (backup or logical mirror using the log file)
  • primary keys are used for communication or synchronization purposes with external systems.

Modifying primary key field value is highly discouraged, even if 4D allows it for specific use cases. If you absolutely need to modify a primary key value (e.g. you used a social security number field as primary key and incorrect values have been entered), it is preferable to disable (remove) the current primary key and to add a new field used as primary key with automatic assignment.

Primary key fields can be long integer type or UUID format (alpha type). In most cases, we recommend the UUID format with the Auto UUID option enabled. Even though the long integer type takes up less disk space, UUIDs have many advantages, for example, facilitating the merge of independent databases, data import / export, implementation of elaborate mirror / replication strategies or synchronization with external systems.

To create a primary key from the Structure editor:

1. Select the field(s) that make up the table’s primary key.
2. Right click and choose Create primary key in the context menu (this command is only displayed for fields whose type is eligible for primary keys):
   
   You cannot have more than one primary key for each table. If a primary key is already specified, a warning dialog box appears indicating that the existing primary key will first be disabled.

The primary key is created immediately. Fields included in the primary key are underlined in the editor and their SQL description displays the PRIMARY KEY keyword.

When you create a primary key in a table that already contains records (using an existing field or adding a new one), 4D checks the conformity of all the values present or automatically performs updating operations: 

• The field(s) belonging to the primary key cannot contain any duplicated values. If any duplicated values for the key exist in the table records, a warning dialog box appears to indicate that it is not possible to create the key because of this.
• If the duplicated values are blank or null values (case of an added key field), you can check the corresponding automatic generation option ( for Alpha fields in or for Longint fields). In this case, 4D examines all the records of the table and automatically assigns a calculated value to each primary key. Note that this process is sequential and may require a significant amount of time if there are a sizable number of records .

Note: The column(s) belonging to the primary key do not accept NULL values.

To remove a primary key from a table:

1. Right click on the table containing the primary key and choose Remove primary key in the context menu:
   
   

A confirmation dialog box appears. Click OK to remove the primary key.

Starting with 4D v14, every new table created in the database contains a primary key by default:

This field, named "ID" by default, is of the Longint type, and has, in particular, the following attributes:

• Unique,
• Reject NULL value input,
• Autoincrement,
• Automatic index

Note: On the Structure Page of the Preferences for the 4D application, you can modify the name and type of the primary keys that are created by default.

You can use this field as is, or change its name and/or its properties if you want (for example, you may want to use an UUID field). You can also delete it if you want to use another field (or fields) as primary key(s).

However, it is strongly recommended to keep at least one primary key in each 4D table.

Note: Default primary keys are not added to tables that are created using the SQL CREATE TABLE command, or tables that are imported into the database.