Providing General Information

When you create a table within a schema, the current schema is used as the table's schema. There are several steps involved in creating a table.

Provide the following information to create a regular table:

1. Enter the table name in the Table Name field. It specifies the name of the table to be created.
   

   Select the Case check box to retain the capitalization of the text entered in the Table Name field. For example, if the table name entered is "Employee", then the table name is created as "Employee".

   The schema name under which the table is created is displayed in the Schema drop-down list.

2. Select table orientation from the Table Orientation drop-down list.
3. Select the tablespace from the Tablespace drop-down list. It specifies the name of the tablespace in which the new table is to be created. If not provided, the default tablespace of the table's schema will be used.
4. Select the table type from the Table Type drop-down list. It specifies the type of the table.
   • Normal: If specified, the table is created as a normal table.
   • Unlogged: If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log, which makes it considerably faster than ordinary tables. However, it is not crash-safe. An unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are also automatically unlogged.

5. Select the required Options.
   • IF NOT EXISTS check box will create the table only if table with same name does not exist.
   • WITH OIDS check box will creat the new table to have OIDs (object identifiers) assigned. If you need a new table with OIDs, choose this option.
   • Select the Fill Factor. The fill factor for a table is a percentage between 10 and 100. 100 (complete packing) is the default value.

     When a smaller fill factor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE operation a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables, smaller fill factors are appropriate. This parameter cannot be set for TOAST tables.

6. Enter the description of the table in the Description of Table box. It specifies a short note on the table.
7. After providing the general information about the table, click Next to define the columns information for the table.

Table below lists the supported fields for each Regular type tables:

Defining Columns

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 on an existing column only for a Regular table:

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

Follow the steps below to define column(s) for the table:

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 Case check box to retain the capitalization of the text entered in Column Name field. For example, if the column name entered is "Name", then the column name is created as "Name".

2. Select the 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 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 type will not be available for selection.

4. Enter the precision/size value of the data type entered in the Precision/Size field. This option is available only if a data type can be defined with precision/size.
5. Select the scale of the data type entered in the Scale field.
6. Choose the following Column Constraints if required:
   • NOT NULL: Specifies that this column is not allowed to contain null values.
   • UNIQUE: Specifies that a column may contain only unique values.
   • DEFAULT: Specifies the value that will be used for this column in case no value is defined.
   • CHECK: Specifies an expression producing a Boolean result which new or updated rows must satisfy for an INSERT or UPDATE operation to succeed.

7. To include comments for Column in Create Regular Table, add column information in Description of Column (Max 5000 chars) text box and click Add button. This is applicable for at Right click New Column dialog box in the object browser and Table Properties Window for regular table.
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.

Following table lists the supported fields for each Regular type tables:

Deleting a Column

Follow the steps to delete a column:

1. Select the required column.
2. Click Delete.

Editing a Column

Follow the steps to edit a column:

1. Select the required 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 to change the location of the column as required in the table. To move a column, select the required column and click Up or Down.

Selecting Data Distribution

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

Select one of the following options for the distribution type:

After selecting data distribution, click Next.

Table below lists the supported options for each Regular type tables:

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.

You can select the following types of constraints from the Constraint Type drop-down list:

• Primary Key
• Unique
• Check

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.

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

Optionally, you can select On Tablespace in which the index associated with a PRIMARY KEY constraint will be created. If this parameter is not provided, the index will be created in the same tablespace as the table.

Fillfactor for a table is a percentage between 10 and 100. The default value is 100 (complete packing). When a smaller fill factor is specified, INSERT operations will pack table pages only up to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE operation a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page.

For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables, smaller fill factors are appropriate. This parameter cannot be set for TOAST tables.

DEFERRABLE: Select this check box to defer this option.

INITIALLY DEFERRED: Select this check box to check the constraint at the set default time.

Click Add in the Constraints group box.

You can remove a primary key from the list using the Delete button.

Mandatory parameters are marked with asterisk (*) in the corresponding field.

Unique

Select the constraint type as UNIQUE in the combo box and enter the constraint name in the text box.

Select column in Available Columns list and click Add. If you need multi-column unique, repeat this step for another column. After adding the first column, the unique name is automatically filled from the table name. You can also change this name.

Optionally, you can select Tablespace in which the index associated with a unique constraint needs to be created. If this parameter is not provided, the index will be created in the same tablespace as the table.

Fillfactor: Refer to Primary Key section for fillfactor information.

DEFERRABLE: Refer to Primary Key section for deferrable information.

INITIALLY DEFERRED: Refer to Primary Key section for initially deferred information.

You can a remove unique from the list using Delete button.

Mandatory parameters are marked with asterisk (*) in the corresponding filed.

Check

Select the constraint type as CHECK in the combo box and enter the constraint name in the text box.

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

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

Then, click Add in Constraints group box. You can also remove a check from the list using Delete button. Mandatory parameters are marked with asterisk (*) in the corresponding field.

After defining all constraints, click Next.

Table below lists the supported options for each Regular type tables:

Defining Indexes

Creating indexes is optional. Indexes are primarily 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. Select the Tablespace in which the index must be created. If not specified, the index is created in the default tablespace.

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 index methods use fill factor in different but roughly analogous 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.

Following table lists the supported fields/options for each Regular type tables:

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.