Create Table

How To Access:

  • Main Menu | Maintenance | Create Table

  • Toolbar Item i_CreateTable

See Also: Restructure Table

Create Table.png

The top panel in the dialog box allows you to enter the table name, path and name of the .INT file, DataFlex file number and select the table style.

Adding a Table

  1. Enter the table name.

  2. Enter the physical location of the INT file. Note: The INT filename defaults to “table name.INT” The physical name is automatically populated with the path for the current workspace.

  3. The file number defaults to the next available file number. You can use the spin button to change this value.

  4. Choose the table style: RECNUM, RowID, or Dynamic Primary Key. See section below on how to choose a Table Style.

  5. Add, modify and delete fields from the new table using the Fields Tab.

  6. Add, modify, and delete indexes from the new table using the Indexes Tab tab.

  7. Select database settings, enter the table’s root, logical, and display names, and determine whether connection information is stored in the INT file using the Table Tab.

  8. When all changes are complete, click the Next button. The Restructure Table dialog box displays. **Note**: If you click Cancel and there are unsaved changes, you are prompted Are you sure you want to cancel? Click Yes to close the dialog box without saving your changes. Click No to continue working in the dialog box.

  9. In the Restructure Table dialog box, you can choose to save your changes in scripts that can be compiled and sent to remote sites to perform automatic restructuring, or click Commit to make changes directly on the server and to generate the DataFlex .FD and .INT files.

Table Style

There are 3 table types to choose from:

  • RECNUM table emulates DataFlex record processing where a unique consecutive positive number (record number) is used to identify each record. The record number designates a record’s relative position in the table. This is useful for legacy applications.

  • rowId table does not use RECNUM to uniquely identify a record, but instead relies on you specifying one or more fields that when combined create a unique record identity. Standard tables use the RowId concepts in Visual DataFlex instead of the older RECNUM concept. A RowId table can take advantage VDF RowId functions.

  • Dynamic Primary Key Table: see below. This option is NOT available for MySQL/MariaDB.

Dynamic Primary Key Table

A dynamic primary key (DPK) table uses a GUID (Globally Unique Identifier), also referred to as UUID (Universally Unique Identifier), field as the primary key. A GUID is a 128-bit quantity generated by an algorithm that nearly guarantees to make it unique.

DPKs are supported by MS SQL, Oracle and PostgreSQL.

Server

Data Type

Notes

MS SQL

uniqueidentifier

Supported on all tables that do not use insert triggers. Insert triggers prevent the driver from returning the newly created primary key. UUIDs can still be used, just not as the primary key.

Oracle

RAW(16)

RAW (16) is the native type for a GUID in Oracle. Not supported for Oracle running on AIX.

PostgreSQL (version 8.2 and above)

uuid

The “uuid-ossp” library, which enables you to generate UUID values server-side, must be installed for DPK support in PostgreSQL. The DPK radio button is disabled if this library is not installed. A tooltip hint over the disabled radio button explains how-to install the library.

Note: MySQL & MariaDB do not have the interface required to support server-side dynamic primary keys. You can however, still use UUIDs but you need to generate them in your code.

Creating a Dynamic Primary Key Table.

  1. Select Table Style Dynamic Primary Key Table.

  2. A UUID field is automatically added and named UUID, change this name if you want.

    Although a UUID is stored as a 128-bit integer on the back end, the assigned DataFlex type is ASCII. VDF treats UUIDs as strings, making searching easy. A UUID is displayed as a sequence of hexadecimal digits, in groups separated by hyphens. For example: {C0E61A92-BE06-4557-AABA-36AB263E0457}.

  3. A function is automatically selected for the default value. You can assign your own function or choose from the drop-down list. The functions available in the drop-down list are the standard server-specific built-in SQL functions for generating unique GUIDs.

    This is the function that is used to generate the dynamic primary key when a record is inserted if you do pass in a value for the field. This is unlike a trigger, which automatically fires whenever a new record is added. Note: Refer to the Flex2SQL Programmer’s Guide for additional information. Requires driver 12.1.6222 or above.

Fields Tab

Allows you to add, modify or delete fields in the table.

  • Field Names: Lists the names of the fields in the table. Click Add Field to add a new field to the end of the table. Select a field name then click Delete Field to delete the field from the table.

  • Name: Field selected in the Field Names panel.

  • DataFlex Type: The DataFlex field type for the column. Defaults to ASCII for new fields but can be changed using the pulldown list.

  • Native Type: The assigned field type mapping on the target database. Allowed types are available using the pulldown list.

  • Length: The number of bytes in the DataFlex field. If the field is an overlap field, click <…> or press the <F4> button to display the Define the Overlap definition popup dialog box.

  • Define the Overlap definition popup dialog box: Use the Define the Overlap definition popup dialog box to enter the overlap field information. This includes the starting field and offset, the ending field and offset and the total length of the overlap field in bytes.

Default Value

If the field is to be created as NOT NULL (automatically NOT NULL if the field is part of an index), then the Default Value specified here is inserted into the column if a value is not provided by the application (left blank). You can select a GUID generator function from the drop-down list, or enter another server-side function to be used to create the ID for the field if a value is not provided by the application.

DefaultValue1.png

DefaultValue2.png

Main Index

Used to select the primary index from the list of available fields.

  1. Click <…> or press the <F4> button to display the Select the main index popup dialog box

  2. Select the Main Index from the list in the popup dialog box. Press OK to select the index and to close the dialog box.

Relates To

Selects the table and field for which this field is a foreign key.

  1. If there is already a relationship, click Clear Relationship to delete an existing relationship.

  2. Position the cursor in the first text box, Relates To, then click <…> or press the <F4> button to display the Select a File popup dialog box.

  3. Select the table and press OK. The second textbox will now be enabled.

  4. Position the cursor in the second text box, then click <…> or press the <F4> button to display the Select the field to fetch popup dialog box.

  5. Select the field and press OK.