New Table Interface

When you create a new table (see Create a Table), a New Table document is opened in the tabbed MDI (see Multiple Document Interface). It consists of the following elements and areas:

The title of the document is initially [new table]. When you specify a name for the table, it is displayed in the tab title. After you modify any field in the document, an asterisk is displayed after the name, indicating that the document has unsaved changes (see Save an Unsaved Document). After saving the table, the asterisk disappears.

You can assign the following table-level properties:

Specifies the name for the table. There is no default for a table name. You must enter a valid Ingres table name. For more information, see Object Naming Rules.

Director performs basic validation on the table name, checking that you have supplied a valid object name. The DBMS performs complete validation checks—such as checking for duplicate table names—when you save the table.

Specifies the structure to use for the table. The default is the default structure type for an Ingres installation. Ingres allows the following structure types:

Keyed storage structure with algorithmically chosen addresses based on key data values

Keyed storage structure in which data is sorted by values in key columns for fast access. The index is static and needs remodification as the table grows.

Keyed storage structure in which data is sorted by values in key columns, but the index is dynamic and grows as the table grows

Stores data in columns. Puts as few columns as possible in one disk block (in most cases only one).

Stores data in rows. Puts as many columns as possible in one disk block.

In one block, values are still kept in compressed vectors. Potentially, all columns in a row can be stored in a disk block. This type is useful for small tables, especially those with a large number of columns; it saves disk space and disk cache memory.

Specifies whether the table is created with journaling turned on. If selected, journaling begins at the next checkpoint.

Specifies whether duplicate rows can be added to the table. If a table allows duplicates, duplicate rows can always be inserted. If a table does not allow duplicates:

• Duplicate rows can be added if the table uses a heap storage structure.

• Single row inserts (insert . . . values) are silently discarded if a duplicate row occurs on a keyed structure.

• Multiple row inserts (insert . . . select) generate an error if a duplicate row occurs on a keyed structure. The entire statement is rolled back.

• When a table is modified from a heap structure to a keyed structure, duplicates are eliminated.

Specifies the size of the default storage block for the table. For more information about page size, see Page Size Specification in the Ingres Database Administrator Guide.

Specifies whether additional locations can be specified for the table

Lets you specify one or more partitions for the table. You must specify columns with partitioning enabled before creating partitions. See Configuring Columns and Partition the Table.

The Select Locations dialog lets you select from a list of existing DATA locations where an entire table or individual columns will be stored. This dialog contains the following fields:

Lists each DATA location as a checkbox. Select each location you want to specify.

For more information, see Specify Locations.

The Partition Specifications dialog lets you configure individual partitions. This dialog contains the following fields:

Lets you specify whether you want to override the default partition names (part1, part2, and so on). If selected, the Names grid appears. Edit each field in the Name column to assign a new name for the partition.

Lets you specify whether you want to select a location for each of the partitions. If selected, a locations drop-down appears; select a DATA location for the partition.

For more information, see Specify Locations.

You can configure the following column properties in the grid:

For more information, see Configuring Columns.

When you first create a new table, the columns grid contains an empty row. To specify a column, you add a row by right-clicking on or below this row and selecting Insert Column from the context menu.

You activate an existing row in the columns grid by clicking anywhere on the row, including the margin. The currently active row in the grid is indicated by a triangle glyph at the beginning of the row.

When you click in a row, the following rules apply:

• If you click a cell, that cell is selected and becomes editable.

• If you click on the Allow Nulls checkbox, the cell is selected and the check state is toggled.

Selecting multiple rows is possible according to operating system conventions. Selecting multiple rows lets you perform the same operation on multiple columns, for example, deleting them using the Delete Column command on the context menu. Options available are listed in Context Menus for New Table Rows.

You can configure the following properties for columns in the columns grid of a new table document:

Specifies a data type for the column. You can select a value from the drop-down list or enter the value manually. When manually entering a data type, auto-complete functionality attempts to fill in the name of the data type based on the predefined items in the drop-down list.

The drop-down list contains all available data types for the table, listed in alphabetical order. Available data types depend on the selected structure of the table; for example, Actian Vector does not allow BLOB data types, so they are not available for that table structure.

All data types that require a length have a default length when displayed in the drop-down list and furnished with auto-complete. You may change the default length value manually.

Specifies whether the column has a default value, and what that value is. The Default Value field becomes editable only when the Default option is checked.

When checked, ensures that no two rows have the same value in a particular column or set of columns

If partitioning is enabled for the table, this checkbox is available. Select it if you wish to partition on the column. For more information, see Partition the Table.

Cells in the grid are validated when the cell loses focus, ensuring that you have entered correct details for the cell, for example, that the name is of the correct format and the data type exists. For more information about naming conventions, see Object Naming Rules.

Database validation occurs when you save the table (see Save a Table). Any errors are displayed in an error message.

You can insert new column rows in the columns grid above the currently selected row.

You may insert multiple columns and edit their fields later. However, all column names and data types must be specified before a table can be saved.

1. Select a row in the column grid above which you want to insert a new column.

2. Right-click the row and select Insert Column from the context menu, or click the Insert Column button.

3. Specify the properties for the new column (see Configuring Columns).

You can delete column rows from a table definition using the context menu. You are not prompted for confirmation when you delete columns from an unsaved table.

2. Right-click the row and select Delete Column from the context menu, or click the Delete Column button.