Defining a Regular Table

A table is a logical structure maintained by a database administrator and consists of rows and columns. You can define a table as a part of your data definitions from the data perspective. Before defining a table, you need to define a database and a schema. This section describes how to use Data Studio to create a table. To define a table in the database, perform the following steps:

1. In the Object Browser pane, right-click Regular Tables, and choose Create Regular Table.
2. Define basic table information, such as the table name and table type. For details, see Providing Basic Information.
3. Define column information, such as the column name, data type schema, data type, and column constraint. For details, see Defining a Column.
4. For details about how to determine table data distribution settings, see Selecting Data Distribution.
5. Define column constraints for different constraint types. Constraint types include PRIMARY KEY, UNIQUE, and CHECK. For details, see Defining Table Constraints.
6. Define table index information, such as the index name and access mode. For details, see Defining an Index.

   On the SQL Preview tab, you can check the automatically generated SQL query. For details, see SQL Preview.

1. Enter a table name. It specifies the name of the table to be created.
   

   Select the Case check box to retain the capitalization of the value of the Table Name parameter. For example, if you enter the table name Employee, the table name will be created as Employee.

   The name of the table schema is displayed in Schema.

2. Select a table storage mode from the Table Orientation drop-down list.
   • ROW: Create a row-store table.
   • COLUMN: Create a column-store table.

3. Select a table type. Its value can be:
   • Normal: a normal table
   • Unlogged: An unlogged table. When data is written to an unlogged table, the data is not recorded in logs. The speed of writing data to an unlogged table is much higher than that of writing data to a common table. However, an unlogged table is insecure. In the case of a conflict or abnormal shutdown, an unlogged table is automatically truncated. The content of unlogged tables is not backed up to the standby node, and no log is automatically recorded when an index is created for unlogged tables.

4. Configure Options.
   • IF NOT EXISTS: Create the table only if a table with same name does not exist.
   • WITH OIDS: Create a table and assign OIDs.
   • Configure Filler Factor. The value range is 10 to 100. 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.

5. Enter table description in the Description of Table text box.
6. Click Next to define the column information of the table.

You can configure the following parameters of a common table.

Select one of the following options for the distribution type:

After selecting data distribution, click Next.

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

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

• 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.

  You can click Delete to delete UNIQUE from the list.

• 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 (*).