Overview

Providing Basic Information

If you create a table in a schema, the current schema will be used as the schema of the table. Perform the following steps to create a common table:

Defining a Column

A column defines a unit of information within a table's row. Each row is an entry in the table. Each column is a category of information that applies to all rows. When you add a table to a database, you can define the columns that compose it. Columns determine the type of data that the table can hold. After providing the general information about the table, click the Columns tab to define the list of table columns. Each column contains name, data type, and other optional properties.

You can perform the following operations only in a common table:

• Deleting a Column
• Editing a Column
• Moving a Column

To define a column, perform the following steps:

1. Enter the column name in Column Name field. It specifies the name of a column to be created in the new table. This must be a unique name in the table.
   

   Select the Case check box to retain the capitalization of the value of the Column Name parameter. For example, if the column name entered is "Name", then the column name is created as "Name".

2. Configure Array Dimensions. It specifies the array dimensions for the column.

   Example: If array dimension for a column is defined as integer [], then it will add the column data as single dimension array.

   

   The marks column in the above table was created as single dimension and subject column as two dimensions.

3. Select the data type of the column from the Data Type drop-down list. For example, bigint for integer values.

   For complex data types,

   • Select the required schema from the Data type Schema drop-down list.
   • Select the corresponding data type from the Data Type drop-down list. This list displays the tables and views for the selected schema.
     

     User-defined data types are not available for selection.

4. Enter the precision/size value of the data type entered in the Precision/Size field. This parameter is valid only when the data type can be defined by precision or size.
5. Select the scale of the data type entered in the Scale field.
6. Choose the following Column Constraints if required:
   • NOT NULL: The column cannot contain null values.
   • UNIQUE: The column may contain only unique values.
   • DEFAULT: The default value used when no value is defined for the column.
   • Check: An expression producing a Boolean result, which new or updated rows must satisfy for an INSERT or UPDATE operation to succeed.

7. To add comments to Column in the Create Regular Table dialog box, add column information in Description of Column (Max 5000 chars) text box and click Add. You can also add comments in the column addition dialog box. You can check comments in general table properties.
8. After you enter all information for new column, click Add. You can also delete a column from a list or change the order of columns. After defining all columns, click Next.

You can configure the following parameters of a column in a common table:

Deleting a Column

To delete a column, perform the following steps:

1. Select a column.
2. Click Delete.

Editing a Column

Follow the steps to edit a column:

1. Select a column.
2. Click Edit.
3. Edit the column details as required and click Update to save changes.
   

   You must complete the edit operation and save the changes to continue with other operations.

Moving a Column

You can move a column in a table. To move a column, select the column and click Up or Down.

Selecting Data Distribution

Data distribution specifies how the table is distributed or replicated among data nodes.

Defining Table Constraints

Creating constraints is optional. A table can have one (and only one) primary key. Creating the primary key is a good practice.

Primary Key

The primary key is the unique identity of a row and consists of one or more columns.

Only one primary key can be specified for a table, either as a column constraint or as a table constraint. The primary key constraint must name a set of columns that is different from other sets of columns named by any unique constraint defined for the same table.

Set the constraint type to PRIMARY KEY and enter the constraint name. Select a column from the Available Columns list and click Add. If you need a multi-column primary key, repeat this step for another column.

Fill Factor for a table is in the range 10 and 100 (unit: %). The default value is 100 (filled to capacity). If Fill Factor is set to a smaller value, the INSERT operation fills only the specified percentage of a table page. The free space of the page will be used to update rows on the page. In this way, the UPDATE operation can place the updated row content on the original page, which is more efficient than placing the update on a different page. Set it to 100 for a table that has never been updated. Set it to a smaller value for largely updated tables. TOAST tables do not support this parameter.

DEFERRABLE: Defer an option.

INITIALLY DEFERRED: Check the constraint at the specified time point.

In the Constraints area, click Add.

You can click Delete to delete a primary key from the list.

Mandatory parameters are marked with asterisks (*).

UNIQUE

Set the constraint type to UNIQUE and enter the constraint name.

Select a column from the Available Columns list and click Add. To configure unique for multiple columns, repeat this step for another column. After adding the first column, the UNIQUE constraint name will be automatically filled. The name can be modified.

Fill Factor: For details, see Primary Key.

DEFERRABLE: For details, see Primary Key.

INITIALLY DEFERRED: For details, see Primary Key.

You can click Delete to delete UNIQUE from the list.

Mandatory parameters are marked with asterisks (*).

CHECK

Set the constraint type to CHECK and enter the constraint name.

When the INSERT or UPDATE operation is performed, and if the check expression fails, then table data is not altered.

If you double-click column in Available Columns list, it is inserted to Check Expression edit line to current cursor position.

In the Constraints area, click Add. You can click Delete to delete CHECK from the list. Mandatory parameters are marked with asterisks (*). After defining all constraints, click Next.

The following table lists the table constraint parameters that can be configured for common tables.

Defining an Index

Indexes are optional. They are used to enhance database performance. This operation constructs an index on the specified column(s) of the specified table. Select the Unique Index check box to enable this option.

Choose the name of the index method from the Access Method list. The default method is B-tree.

The Fill factor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (adding new largest key values). If pages subsequently become completely full, they will be split, leading to gradual degradation in the index's efficiency. B-trees use a default fill factor of 90, but any integer value from 10 to 100 can be selected. If the table is static, then a fill factor of 100 can minimize the index's physical size. For heavily updated tables, an explain plan smaller fill factor is better to minimize the need for page splits. Other indexing methods use different fill factors but work in similar ways. The default fill factor varies between methods.

You can either enter a user-defined expression for the index or you can create the index using the Available Columns list. Select the column in the Available Columns list and click Add. If you need a multi-column index, repeat this step for other columns.

After entering the required information for the new index, click Add.

You can also delete an index from the list using the Delete button. After defining all indexes, click Next.

You can configure the following parameters of an index in a common table.

SQL Preview

Data Studio generates a DDL statement based on the inputs provided in Create New table wizard.

You can only view, select, and copy the query. You cannot edit the query.

• To select all queries, press Ctrl+A or right-click and select Select All.
• To copy the selected query, press Ctrl+C or right-click and select Copy.

Click Finish to create the table. On clicking the Finish button, the generated query will be sent to the server. Any errors are displayed in the dialog box and status bar.