Table Editor
The following topics describe how to work with tables in the ZenCC Table Editor:
Table Editor Concepts
Table Editor concepts are covered in the following topics:
• Overview
Overview
Table Editor is one of the editor windows within Zen Control Center (ZenCC). Table Editor is a special type of editor that contains multiple pages. The pages are represented by tabs across the bottom of the editor. The editor allows you to add, delete, or change the characteristics of columns within a table. The table may be one newly created or an existing table that you want to edit.
To modify tables with Table Editor, you must have full administrator rights on the machine on which the database engine is running, even if you are a member of the Zen_Admin group. See Granting Administrative Rights for the Database Engine and Database Security in Advanced Operations Guide.
Caution! Back up all your data definition files (DDFs) and data files before you perform functions through Table Editor. This tool gives you the ability to modify your database table definitions and data. If you inadvertently set the options incorrectly or enter incorrect data, you could change your files in an irreversible manner. Full recovery is possible from a backup.
Table Editor Pages
Table Editor displays the following pages that serve as work areas:
You can switch among these pages by clicking their tabs at the bottom of the Table Editor panel.
Note: Save your changes before switching pages.
Columns Page
The Columns page lets you add, delete, modify columns, and set primary keys. See Columns Page for a description of the areas on the Columns page. See Columns Tasks for tasks that you can do on the page.
Indexes Page
The Indexes page lets you add and modify indexes and index segments. See Indexes Tasks for tasks that you can do on the page.
Foreign Keys Page
The Foreign Keys page lets you add and modify foreign keys. See Foreign Keys Tasks for tasks that you can do on the page.
SQL View Page
The SQL View page lets you view the CREATE TABLE and ALTER TABLE statements that apply to the table. Note that SQL View is display only. You cannot modify the SQL statements but you can copy them. See SQL View Tasks for tasks that you can do on the page.
The SQL View page reflects changes made on the other pages as explained below.
CREATE Statements
The CREATE TABLE statement shows the SQL used to create the table. For a new table, one that has not yet been saved, the CREATE TABLE statement reflects the SQL used to implement edits from the other three tabbed dialogs. For example, if you create a new table named MyNewTable, SQL View initially shows the following CREATE statement:
CREATE TABLE MyNewTable(
);
If you were to add two CHAR columns to the new table on the Columns page, SQL View reflects this in the CREATE statement:
CREATE TABLE MyNewTable(
"FirstName" CHAR(20),
"LastName" CHAR(30)
);
If a table has been saved (already exists), then the CREATE TABLE statement shows the SQL that created the table. For example, if you were to edit the Course table provided with the sample database Demodata, SQL View shows the following CREATE statements:
CREATE TABLE Course(
"Name" CHAR(7) NOT NULL ,
"Description" CHAR(50),
"Credit_Hours" USMALLINT,
"Dept_Name" CHAR(20) NOT NULL
);
CREATE INDEX Course_Name ON Course("Name");
CREATE UNIQUE INDEX DeptName ON Course("Dept_Name");
Note: To create tables, database security must be disabled or in the database security properties your user account must have Create Table permission.
ALTER Statements
When you are editing an existing table, the ALTER TABLE statements show what SQL is used to implement edits from the other three Table Editor pages. For example, suppose that you edit the Course table provided with the sample database Demodata. On the Index page, you change the sort order for the Name index segment from ascending to descending. SQL View shows the following ALTER statements:
DROP INDEX Course.Course_Name;
CREATE INDEX Course_Name ON Course("Name" DESC);
Saving a table clears the ALTER TABLE statements because no changes are pending.
Data Types
See Zen Supported Data Types in SQL Engine Reference for a list of the data types supported by the database engine. You may use any data types listed there that also appear in the Type selection list on the Columns page in Table Editor.
Null Values
The ability to modify the null attribute of a column is subject to the following restrictions:
• The target column cannot have a primary or foreign key constraint defined on it.
• If converting the old type to the new type causes an overflow (arithmetic or size), the ALTER TABLE operation is aborted.
• If a nullable column contains NULL values, the column cannot be changed to a nonnullable column.
If you must change the data type of a key column, you can do so by deleting the index key, changing the data type, and reentering the key. Keep in mind that you must ensure that all associated index key columns in the database remain synchronized.
For example, if you have a primary index key in table T1 that is referenced by foreign keys in tables T2 and T3, you must first delete the foreign keys. Then you delete the primary key and change all three columns to the same data type and size. Finally, you must reenter the primary key and then the foreign keys.
For more information on nulls, see the following:
Table Editor Graphical User Interface
The Table Editor interface provides work areas called pages for the following:
Columns Page
The Columns page can be considered the primary page of Table Editor just as columns and rows are central to tables. The following illustration shows the Columns page of Table Editor. The table below the image describes the GUI objects. Click an area of the image for which you want more information.
See also Columns Tasks for the tasks that you perform on the Columns page:
GUI Object | Description | Related Information |
|---|---|---|
Column Name | Specifies the alpha-numeric name for the column. | Relational Engine Limits in SQL Engine Reference SQL Grammar Support in ODBC Guide Identifier Restrictions in Advanced Operations Guide (Return to Columns Page image) |
Type | Specifies the data type of the column. | Zen Supported Data Types in SQL Engine Reference (Return to Columns Page image) |
Size | Specifies how many bytes are permitted for the data type. A shaded cell indicates that size does not apply. | (Return to Columns Page image) |
Precision | Specifies the number of significant digits for floating point values. A shaded cell indicates that precision does not apply. | (Return to Columns Page image) |
Scale | Specifies the number of significant digits that are to the right of the decimal point for floating point values. A shaded cell indicates that scale does not apply. | (Return to Columns Page image) |
Null | Specifies whether NULL values are allowed for the data type. A shaded square (  ) indicates that nulls do not apply to the data type. A checked square ( ) indicates that NULL values are allowed for the data type. | (Return to Columns Page image) |
Case | Specifies whether the database engine uses case-sensitive or case-insensitive comparisons when searching for character values in the database. A checked square ( ) indicates that case-insensitive values are used. A shaded square () indicates that case sensitivity does not apply to the data type. | (Return to Columns Page image) |
Collate | Specifies that an alternating collating sequence (ACS) or International Sort Rule (ISR) is used for sorting. Contains the ACS path name or ISR name. | (Return to Columns Page image) |
Default | Specifies a default value for the column. The default value is used if you perform a SQL INSERT for a row but do not provide a value for the column. | (Return to Columns Page image) |
Indexes Page
The Indexes page allows you to add and delete indexes. The Indexes page is explained within the context of the tasks that you perform for indexes. See Indexes Tasks.
Foreign Keys Page
The Foreign Keys page allows you to add and delete foreign keys. The Foreign Keys page is explained within the context of the tasks that you perform for foreign keys. See Foreign Keys Tasks.
SQL View Page
The SQL View page displays, and allows you to copy, the SQL statements used to create or alter the table. The CREATE TABLE panel displays the SQL with which you could create the same table. The ALTER TABLE panel reflects any editing changes to an existing table that you make with Table Editor. When you save the table changes, the ALTER TABLE panel is cleared and any ALTER statements becomes part of the CREATE TABLE statement.
The SQL View page is further explained within the context of the tasks that you can perform. See SQL View Tasks.
Table Editor Tasks
The following tasks are done with Table Editor. They are divided into the following categories:
Category | Description |
|---|---|
Overall use of Table Editor | |
Using the Columns page | |
Using the Indexes page | |
Using the Statistic page | |
Using the SQL View page |
Note: You cannot save the changes to the structure of a table if any queries in SQL Editor are holding the table open. Close the SQL Editor holding open the table then save the changes.
General Tasks
General tasks apply to the overall use of the tool.
Getting Started
Data
Columns Tasks
Column tasks apply to the Columns page.
Indexes Tasks
Index tasks apply to the Indexes page.
Foreign Keys Tasks
Foreign keys tasks apply to the Foreign Keys page.
SQL View Tasks
SQL view tasks apply to the SQL View page.
General Tasks
To start Table Editor for a new table
1. Start ZenCC if it is not already running. (See Starting ZenCC on Windows.)
2. Expand the Engines and Databases nodes in Zen Explorer.
3. Right-click the database to which you want to add the new table.
4. Click New > Table and type the name for new table.
Tip... For a list of database object lengths and invalid characters, see Identifier Restrictions in Advanced Operations Guide.
Note that no two data files in the same directory can share the same file name and differ only by file name extension. For example, invoice.btr and invoice.mkd are not allowed in the same directory. The reason for this restriction is that the database engine ignores the file name extension and sees invoice.btr and invoice.mkd as the same file.
5. Click Finish.
To start Table Editor for an existing table
1. Start ZenCC if it is not already running. (See Starting ZenCC on Windows.)
2. Expand the Engines and Databases nodes in Zen Explorer.
3. Under the database where you find the table, right-click it and select Edit.
To work with columns
1. Perform the steps for To start Table Editor for an existing table or To start Table Editor for a new table.
2. In the panel for the table, click the Columns page tab.
To work with indexes
1. Perform the steps for To start Table Editor for an existing table or To start Table Editor for a new table.
2. In the panel for the table, click the Indexes page tab.
To work with foreign keys
1. Perform the steps for To start Table Editor for an existing table or To start Table Editor for a new table.
2. In the panel for the table, click the Foreign Keys page tab.
To view SQL statements applicable to the table
1. Perform the steps for To start Table Editor for an existing table or To start Table Editor for a new table.
2. In the panel for the table, click the SQL View page tab.
To view table data while using Table Editor
1. Perform the steps for To start Table Editor for an existing table or To start Table Editor for a new table.
2. If the Grid window view is not displayed, click Window > Show View > Grid.
By default, the Grid shows all data for the table (the result of a SELECT * FROM table statement).
Caution! The Grid allows you to change data by changing grid cell values. See Grid Tasks.
To identify tables with changes that have not been saved
1. Observe the Table Editor tab at the top. The tab contains the name of the table being created or edited. An asterisk (*) precedes the name if any modifications have occurred to columns, indexes, or foreign keys but not yet saved. 
To save changes for the table being edited
Note that you cannot undo or redo changes to a table once the table has been saved.
1. Click File > Save or click 
.
.Note: You cannot save the changes to the structure of a table if the table is open in SQL Editor. Close the SQL Editor referencing the table then save the changes.
To save changes for all tables being edited
Note that you cannot undo or redo changes to tables once the tables have been saved.
1. Click File > Save All.
Note: You cannot save the changes to the structure of a table if the table is open in SQL Editor. Close the SQL Editors referencing the tables then save the changes.
To undo changes or to redo changes
1. In the toolbar, click 
to undo an action; click 
to redo an action.
to undo an action; click to redo an action.If multiple actions have occurred since the last save, you can repeatedly click the undo or redo toolbar buttons. When no more actions are available for undo or redo, the toolbar button becomes disabled.
Note that you cannot undo or redo changes to a table once the table has been saved.
Columns Tasks
To insert a column between existing columns
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Right-click an existing column row above which you want to insert a new column.
3. Click Append Column.
The new column appears above the existing column row. The default name of the inserted column is columnn, where n is a number that automatically increments by one. The first column you insert is column0, the second is column1 and so forth.
Tip... You can also insert a column by clicking an existing column row, then pressing Ctrl+Insert or clicking 
. Repeating either action inserts a series of columns in succession.
. Repeating either action inserts a series of columns in succession.4. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To insert a column at the end
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Right-click anywhere on the empty column row below the last existing column row, then click Add Column.
or
Click the Column Name cell on the empty column row below the last existing column row and start typing a name for the column.
The default name of the inserted column is columnn, where n is a number that automatically increments by one. (The first column you insert is column0, the second column1 and so forth.)
Tip... You can also insert a column at the end by clicking or pressing Ctrl+Insert.
Repeating either action inserts a series of columns in succession. The insert action automatically adds the new column to the end.
or pressing Ctrl+Insert. Repeating either action inserts a series of columns in succession. The insert action automatically adds the new column to the end.
3. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To select a column or multiple columns
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click 
(the column selection icon on the far left of the column row) for the desired column.
(the column selection icon on the far left of the column row) for the desired column.To select multiple columns, press and hold Shift or Ctrl, then click for the desired additional columns.
for the desired additional columns.To delete a column
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Right-click anywhere on the desired column row.
3. Click Drop Column.
Tip... You can also delete a column by clicking an existing column row, then pressing Ctrl+Delete or clicking 
.
.You can also delete multiple columns by selecting multiple columns rows. See To select a column or multiple columns.
4. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To specify a column name
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click in the Column Name cell for the desired column.
3. Delete the existing column name.
4. Type the name you want.
Tip... For a list of database object lengths and invalid characters, see Identifier Restrictions in Advanced Operations Guide.
Also, as a general rule, avoid using reserved words for column names. See Reserved Words in SQL Engine Reference. See also SQL Grammar Support in ODBC Guide.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To set a column data type
The data in your database is converted if you change a column data type. For a listing of data types, see Zen Supported Data Types in SQL Engine Reference.
Changing a column data type sets the defaults for that type on the following: size, scale, precision, default, and collate.
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click in the Type cell for the desired column.
3. Open the list for data types (click 
).
).4. Scroll to the data type you want. (You may also type the first letter of the desired data type to scroll. Repeatedly typing the first letter scrolls to each data type that begins with that letter.)
5. Click the data type you want.
6. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.Note: Changing a data type on a column that contains a default value causes the default value to be cleared; reset the value if necessary.
To set a column size
Data in your database is truncated if you change the column to a smaller size for the following data types:
• CHAR
• NUMERIC
• VARCHAR
1. Be sure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click the Size cell for the desired column.
You can set a size only for applicable data types, such as CHAR. If size is not applicable, the grid cell is shaded and you will be unable to edit it.
3. Delete the existing value.
4. Enter the new value.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To set column precision
Precision specifies the number of significant digits for floating point values.
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click in the Precision cell for the desired column.
You can set precision only for applicable data types, such as DECIMAL. If precision is not applicable, the grid cell is shaded and you will be unable to edit it.
3. Delete the existing value.
4. Enter the new value.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To set column scale
Scale specifies the number of significant digits to the right of the decimal point for floating point values.
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click in the Scale cell for the desired column.
You can set a scale value only for applicable data types, such as NUMERIC. If scale is not applicable, the grid cell is shaded and you will be unable to edit it.
3. Delete the existing value.
4. Enter the new value.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To set a column to allow or disallow nulls
For additional information about nulls, see Rebuild Tool Concepts in Advanced Operations Guide, INSERT in SQL Engine Reference, and Null Value in the Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click the option box in the Null cell for the desired column.
You can allow nulls only for applicable data types. A shaded square indicates that null values do not apply to the data type.
Option State | Meaning |
|---|---|
| Nulls specified |
 | Nulls allowed but not specified |
| Nulls do not apply |
Also see Null Values.
3. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To set case sensitivity for a column
Case sensitivity does not apply if the key uses an alternate collating sequence (ACS). You cannot specify case sensitivity and also use an ACS.
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click the option box in the Case cell for the desired column.
You can set a collating sequence only for applicable data types. A shaded square indicates that case sensitivity does not apply to the data type.
Option State | Meaning |
|---|---|
| Case insensitive |
| Case sensitive |
| Case does not apply |
By default, Zen is case sensitive when sorting string keys. Uppercase letters are sorted before lowercase letters. If you specify case insensitive, values are sorted without distinguishing case.
3. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To set a column collating sequence
For additional information about collating sequences, see Manipulating Btrieve Data Files with the Maintenance Tool in Advanced Operations Guide and Alternate Collating Sequences in the Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
If you use an alternate collating sequence (ACS) or International Sort Rule (ISR), you cannot specify case sensitivity. Case sensitivity does not apply if the key uses an ACS or ISR.
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click the Collate cell for the desired column.
You can set an ACS or ISR only for applicable data types. If collating sequence is not applicable, the grid cell is shaded and you will be unable to edit the Collate cell.
3. Delete the existing value, if present.
4. Enter the ACS path name or ISR name.
Zen supplies the ACS file upper.alt in its installed Samples folder. To use this file, you would enter file_path\Zen\samples\upper.alt.
Upper.alt treats upper and lower case letters the same for sorting. For example, if a database has values abc, ABC, DEF, and Def, inserted in that order, the sorting with upper.alt returns as abc, ABC, DEF, and Def. (The values abc and ABC, and the values DEF and Def are considered duplicates and are returned in the order in which they were inserted.) Normal ASCII sorting sequences upper case letters before lower case, such that the sorting would return as ABC, DEF, Def, abc.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To set a column default
A default value is used in a SQL INSERT for a row if no value is provided for a column. You can set certain scalar functions to serve as the default for certain data types.
1. Check that the Columns page of Table Editor is active. See To work with columns.
2. Under Default, click the cell for the desired column.
3. To change the default, delete any existing value and enter a new one.
If the scalar function you choose is not applicable for the data type, then the grid cell does not accept your entry. For date and time stamp values, the following table shows the compatibility of scalar functions with data types.
Data Type | Scalar Function |
|---|---|
DATE | • CURDATE() • NOW() |
TIME | • CURTIME() • NOW() |
Time stamp types | • CURRENT_TIMESTAMP() • NOW() • SYSDATETIME() • SYSUTCDATETIME() |
Note: Function names are not case-sensitive. NOW() and now() are equivalent. The empty parentheses are required. | |
4. Save the file before changing pages in Table Editor.
Note: When you use a scalar function as a default, insert operations may return warnings about data truncation if the data type cannot hold the returned function value. For example, setting NOW() as default on a column of type DATE truncates the time portion of the returned value. The warning does not prevent successful insertion of the truncated value.
To set or remove a column as a primary key
Note that you cannot set a primary key on a column that allows nulls.
1. Ensure that the Columns page of Table Editor is active. If needed, see To work with columns.
2. Click , the column selection icon on the far left of the column row, for the desired columns.
, the column selection icon on the far left of the column row, for the desired columns.To select multiple columns, press and hold Shift or Ctrl, then click for the desired additional columns.
for the desired additional columns.3. Click 
, the primary key icon.
, the primary key icon.If the column or columns are not a primary key, the action sets them as a primary key.
If the column, or if any of the columns when multiple columns are selected, is already a primary key, the action removes the setting from all columns.
For example, suppose that column 1 is a primary key and you want columns 1, 2, and 3 to be the primary key. You press and hold Ctrl, then click columns 1, 2, and 3. When you click the primary key icon, it is removed from column 1 but not added to columns 2 and 3. If you click the primary key icon again, then columns 1, 2, and 3 are designated as primary keys.
4. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.Index Tasks
To create an index
Tables created with an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column have unique indexes automatically created on these columns. You cannot create a second index on one of these columns unless it is included as part of a multiple-segment index. See also AUTOINCREMENT in SQL Engine Reference.
Table Editor permits you to include an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column in the Indexes list if you have not saved the table. However, the database engine returns an error when you attempt to save the table. After you delete this column from the list, you may then save the table.
1. Check that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click Add.
3. In the New Index dialog, enter the name of the new index and click OK.
Tip... For a list of database object lengths and invalid characters, see Identifier Restrictions in Advanced Operations Guide.
The new index appears in the Indexes list and the Index Segment Details display. Note that the first column is populated into the Columns list. 
Note: New indexes are created by default as Normal.
4. In the Columns list, select the column to designate for the index or index segment.
Caution! Indexes must have at least one column designated. If you do not select a column for the index, then the first column remains selected.
The default sort order is ascending. If you need a descending sort order, select Descending from the Sort Order list for the Column you want changed.
Note: Some data types, such as LONGVARBINARY, cannot be used for an index. Columns with such data types are not valid choices.
5. Continue selecting columns from the list until all the segments are added.
Note that a column can be selected for an index only once. Once selected, the column is removed from the list of choices because the column has already been designated in the index.
6. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.See also Creating Indexes in Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
To create a unique index
Tables created with an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column have unique indexes automatically created on these columns. You cannot create a second index on one of these columns unless it is included as part of a multiple-segment index.
Table Editor permits you to include an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column in the Indexes list if you have not saved the table. However, the database engine returns an error when you attempt to save the table. After you delete this column from the list, you may then save the table.
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click Add.
3. In the New Index dialog, enter the name of the new index and click OK.
Tip... For a list of database object lengths and invalid characters, see Identifier Restrictions in Advanced Operations Guide.
The new index appears in the Indexes list and the Index Segment Details display.
Note: New indexes are created by default as Normal.
4. Select Unique in the Index Segment Details area to designate the index as unique.
Selecting Unique disables duplicatability, restricting duplicates.
Note that the first column is populated into the Columns list.
5. In the Columns list, select the Column to designate for the Index or Index Segment.
Caution! Indexes must have at least one Column designated. If you do not select a Column for the Index, the first column remains selected.
The default sort order is Ascending. If you need a descending sort order, select Descending from the Sort Order list for the Column you want changed.
Note: Some data types, such as LONGVARBINARY, cannot be used for an index. Columns with such data types are not valid choices.
6. Continue selecting columns from the list until all the segments are added.
Note that a column can be selected for an index only once. Once selected, the column is removed from the list of choices because the column has already been designated in the index.
7. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.See also Creating Indexes in Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
To create a partial index
Tables created with an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column have unique indexes automatically created on these columns. You cannot create a second index on one of these columns unless it is included as part of a multiple-segment index. See also AUTOINCREMENT in SQL Engine Reference.
Table Editor permits you to include an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column in the Indexes list if you have not saved the table. However, the database engine returns an error when you attempt to save the table. After you delete this column from the list, you may then save the table.
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click Add.
3. In the New Index dialog, enter the name of the new index and click OK.
Tip... For a list of database object lengths and invalid characters, see Identifier Restrictions in Advanced Operations Guide.
The new index appears in the Indexes list and the Index Segment Details display.
Note: New indexes are created by default as Normal.
4. Select Partial in the Index Segment Details area to designate the index as partial.
Note that the first column is populated into the Columns list.
5. In the Columns list, select the Column to designate for the Index or Index Segment.
Caution! Indexes must have at least one Column designated. If you do not select a Column for the Index, the first column remains selected.
The default sort order is Ascending. If you need a descending sort order, select Descending from the Sort Order list for the Column you want changed.
Note: Partial Indexes are restricted to columns with a data type of CHAR or VARCHAR and that are designated as the only or last segment in an Index.
6. Continue selecting columns from the list until all the segments are added.
Note that a column can be selected for an index only once. Once selected, the column is removed from the list of choices because the column has already been designated in the index.
7. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.See also Creating Indexes in Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
To modify an existing index
The database engine creates some indexes, such as IDENTITY, SMALLIDENTITY, or BIGIDENTITY column indexes and primary key indexes. These indexes are read-only and cannot be modified.
Table Editor permits you to include an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column in the Indexes list if you have not saved the table. However, the database engine returns an error when you attempt to save the table. After you delete this column from the list, you may then save the table.
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
3. In the Index Segment Details, modify the them as desired.
4. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To delete an index
The database engine creates some indexes, such as IDENTITY, SMALLIDENTITY, or BIGIDENTITY column indexes and primary key indexes. These indexes are read-only and cannot be deleted.
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
3. With the Index you want to delete selected, click Delete in the Indexes list.
4. Click Yes to confirm the deletion.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To insert an index segment
For detailed information about segments, see Segmentation in the Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
Tables created with an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column have unique indexes automatically created on these columns. You cannot create a second index on one of these columns unless it is included as part of a multiple-segment index. See also AUTOINCREMENT in SQL Engine Reference.
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
The Index Segment Details appears and lists the selected Index Segments.
3. Click in the first empty Columns cell, then open the Columns list (click ).
).
4. From the list, click the desired column for the segment.
Note: Some data types, such as LONGVARBINARY, cannot be used for an index. Columns with such data types are not valid choices.
The default sort order is Ascending. If you need a descending sort order, select Descending from the Sort Order list for the Column you want changed.
5. Continue selecting columns from the list until all the segments are added.
Note that a column can be selected for an index only once. Once selected, the column is removed from the list of choices because the column has already been designated in the index.
6. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To modify an index segment
For detailed information about segments, see Segmentation in the Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
Tables created with an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column have unique indexes automatically created on these columns. You cannot create a second index on one of these columns unless it is included as part of a multiple-segment index. See also AUTOINCREMENT in SQL Engine Reference.
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
3. In Index Segment Details, click the desired segment in the Columns cell then open the list of columns (click ).
).
4. From the Columns list, click the desired column designated as the index segment.
Note: Some data types, such as LONGVARBINARY, cannot be used for an index. Columns with such data types are not valid choices.
The default sort order is Ascending. If you need a descending sort order, select Descending from the Sort Order list for the Column you want changed.
5. Continue selecting columns from the list until you have completed all segment modifications.
Note that a column can be selected for an index only once. Once selected, the column is removed from the list of choices because the column has already been designated in the index.
6. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To delete an index segment
Note: Each index requires a minimum of one segment. To delete an index that has only one segment, delete the index itself.
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
The Index Segment Details appears, listing all the designated index segments.
3. Click the desired index segment.
4. With the index segment you want to delete selected, click Delete in the Index Segment Details Columns list. 
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To arrange the order of index segments
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
3. In the Index Segment Details, click the index segment you want to reorder.
4. Click Up to move the segment toward the top of the segment grouping, or Down to move the segment toward the bottom.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To specify a sort order for an index
For detailed information about sort order, see Sort Order in the Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
The Index Segment Details appears.
3. Click in the Sort Columns cell then open the list of sort choices (click ).
).4. From the Sort Order list, click Ascending or Descending.
The default sort order when an index segment is created is ascending.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To allow duplicates in an index
For detailed information about duplicates, see Duplicatability in the Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
The Index Segment Details appears.
3. Clear the Unique option in the Index Segment Details area by selecting one of the other options (Partial or Normal).
Note: By default, indexes are created as Normal, allowing duplicates.
4. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To specify index as modifiable
For detailed information about modifiability, see Modifiability in the Zen Programmer’s Guide, which is part of the Zen Software Development Kit (SDK).
1. Ensure that the Indexes page of Table Editor is active. If needed, see To work with indexes.
2. Click the desired index in the Indexes list.
3. In the Index Segment Details, click Allow Modifications option.
A check mark in the box indicates that the index value can be modified. Lack of a check mark indicates that the index value cannot be modified.
Option State | Meaning |
|---|---|
| Values can be modified |
| Values cannot be modified |
| Modifiable does not apply |
The default for all SQL data types is that the index column is modifiable.
4. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.Foreign Keys Tasks
To add a foreign key
Note that at least one table in the database must have a primary key or you cannot add a foreign key.
1. Ensure that the Foreign Keys page of Table Editor is active. If needed, see To work with foreign keys.
2. Click Add.
3. Type the name that you want for the new foreign key.
Tip... For a list of database object lengths and invalid characters, see Identifier Restrictions in Advanced Operations Guide.
4. Click OK.
The new foreign key appears in the Foreign Keys list and the Foreign Keys Details display.
5. Click for Select Primary Table to display the list of tables permissible as primary tables.
for Select Primary Table to display the list of tables permissible as primary tables.
6. Click the desired table in the list (only tables with a primary key appear in the list).
The primary fields in the table appear in Primary Table Fields column.
7. Match fields in the foreign table with fields in the primary table:
Click the empty cell in the Foreign Table Fields column for the corresponding field in the Primary Table Fields, then click to display the list of permissible fields.
to display the list of permissible fields.
Note: The data type and size of the fields must match. The list of Foreign Table Fields contains only fields that are the same data type and size as the primary table field being matched.
8. Click the desired field in the list.
9. Repeat steps 7 and 8 to match each field listed in the Primary Table Fields column with a field in the Foreign Table Fields column.
10. Click the desired referential integrity rule: Delete Restrict or Delete Cascade.
Zen allows a circular delete cascade on a table that references itself. Because of this, use delete cascade with caution. See Delete Restrict and Delete Cascade, both in Advanced Operations Guide.
11. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To modify a foreign key
1. Ensure that the Foreign Keys page of Table Editor is active. If needed, see To work with foreign keys.
2. Click the desired foreign key in the Foreign Keys list.
3. In the Foreign Keys Details list, select the primary table, match foreign table fields to primary table fields, and set the referential integrity rule.
4. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.To delete a foreign key
1. Ensure that the Foreign Keys page of Table Editor is active. If needed, see To work with foreign keys.
2. Click the desired foreign key in the Foreign Keys list.
3. Click Delete.
4. Click Yes to confirm the deletion.
5. Click File > Save or before changing pages in Table Editor.
before changing pages in Table Editor.SQL View Tasks
To copy SQL statements
1. Ensure that the SQL View page of Table Editor is active. If needed, see To view SQL statements applicable to the table.
2. Position the cursor in the desired statement view: CREATE Statement or ALTER Statement.
3. With the mouse, select the desired text.
Tip... You can press Ctrl+A to select all of the text.
4. Right-click then click Copy (or press Ctrl+C).
To maximize or restore view of SQL statements
1. Ensure that the SQL View page of Table Editor is active. If needed, see To view SQL statements applicable to the table.
2. For the desired statement view, CREATE Statement or ALTER Statement, click the icon in the upper right corner of the view:
Icon | Action |
|---|---|
 | Maximizes the statement view. |
 | Restores the statement view to its size prior to maximizing. |
Last modified date: 10/31/2023