Creating a Table

About this task

This topic explains the steps to create a table using InformixHQ UI.

There are five types of tables:

  1. Standard table
  2. Raw table
  3. External Fixed table
  4. External Delimited table
  5. External Informix table

Standard & Raw table types are almost similar & three external table types are almost similar. This topic explains how to create both table types.

Procedure

To create a table, follow the steps given below:
  1. Go to the Schema Manager in InformixHQ.
  2. Select desired database and click on menu option (3 dots) next to Select Database dropdown.
  3. Click on Create Table option from dropdown to create a table

    To create a Standard or Raw type table:

    1. Enter mandatory fields such as Table Name and Table Owner.
    2. Select Standard(default) or Raw type from Table Type dropdown.
    3. Click on Add Column for adding columns to the table.
    4. To cancel Create Table operation click on Cancel button.
    5. Next button will be enabled after user is done adding columns to the table.
    1. By clicking on Add Columns button pop up will appear to add column details.
    2. Enter values for all mandatory fields(*).
    3. To give a constraint on any column, click on Column Check Constraint checkbox.
    4. Multiple columns can be added using the same pop up.
    5. To go back to the main screen, click on Close button or cross icon in the upper right corner.
    1. Once columns are added, user can view, edit, delete any of the columns.
    2. Table level constraint can be added on this screen by clicking on Table Check Constraint checkbox.
    3. Once details related to the columns are finalized, clicking on Next button takes user to add constraint page.
    1. This screen is for adding a constraint like primary key, foreign key, unique key to a table.
    2. To assign primary key to a table, give Primary Key Name & select Primary Key Columns.
    1. To add Foreign key constraint, click on Add button in Foreign key section.
    2. To assign a foreign key for a table, give Foreign Key Name & map Referenced Column with Table Column.
    1. To add a Unique key constraint, click on Add button in Unique key section.
    2. To assign a unique key for a table, give Unique Key Name & select Available Columns from the list. This will enable the Add button.
    1. Once constraints are added user can go to either View SQLor Advance Table Options
    1. Modify advance table level options using the screen given below. For example, changing lock mode, storage scheme, update statistics, etc.
    1. Once advance table option are set, click on View Query & Create button to view SQL query for creating the table.
    2. After clicking on View Query & Create button from Advance Table Options or on View SQLbutton from Add Constraints screen, user will be able to view 'create table' query as shown in the screen given below.
    3. Click Create button to create the table.
    4. If table is created successfully, information status message will be shown and user will be taken back to Schema Manager page.
    5. If table creation fails, error status message is displayed and all the create table related queries will be rolled back
    6. To go back to modify any properties click Back button and to cancel the operation of create table click on Cancel button.

    External table type:

    To create an External type table:

    1. Enter mandatory fields such as Table Name and Table Owner.
    2. Select one of External Fixed, External delimited, External Informix table type from Table Type dropdown.
    3. Click on Add Columns for adding columns to the table.
    4. To cancel Create Table operation, click on Cancel button.
    5. Next button will be enabled once user adds columns to the table.
    1. By clicking on Add Column button, a pop up will appear to add column details.
    2. Enter all the mandatory field values(*).
    3. User can add multiple columns using the same pop up.
    4. To go back to the main screen, click on Close button or Cross icon.
    1. Once columns are added, user can view, edit , delete any of the columns.
    2. Once column details are finalized click on Next button to go to External Table Options
    1. Provide information for external table options & add mandatory data file by clicking on +Add button.
    1. Following pop up is used to add data files to an external table.
    1. Once data files are added, user can view, edit , delete any of the data files.
    2. Once External table options are finalized click on View Query & Create button to view SQL query for creating the table.
    3. User can either go Back or Cancel the operation using respective buttons.
    1. After clicking on View Query & Create button from the external table option, user will be able to view create table query as shown in the screen below.
    2. Click Create button to create the table.
    3. If table is created successfully information status message will be shown and user will be taken back to Schema Manager page.
    4. If table creation fails, error status message will be displayed and all the create table queries will be rolled back.
    5. To go back to modify any properties, click Back button and to cancel the operation of create table click on Cancel button.