Library > Products > Pervasive PSQL > Pervasive PSQL v9 SP2 (9.5) > User's Guide > Table Editor > Table Editor Tasks

PreviousUser's Guide (v9 SP2 (9.5) revision 1) Next

Table Editor Tasks

Chapter contents

This section explains the tasks that you perform with Table Editor. The tasks are divided into the following categories:

Category
Description
General Tasks
Orient you to the overall use of Table Editor
Columns Tasks
Apply to using the Columns page
Indexes Tasks
Apply to using the Indexes page
Foreign Keys Tasks
Apply to using the Statistic page
SQL View Tasks
Apply to 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 PCC if it is not already running. (See Starting PCC .)
  2. Expand the Engines and Databases nodes in Pervasive PSQL Explorer (click the icon to the left of each node).
  3. Right-click on the database to which you want to add the new table.
  4. Click New 4 Table.

    5. Type the name for new table, which can be a maximum of 20 bytes.

  5. Click Finish.

To start Table Editor for an existing table

  1. Start PCC if it is not already running. (See Starting PCC .)
  2. Expand the Engines and Databases nodes in Pervasive PSQL Explorer (click the icon to the left of each node).
  3. Right-click on the table that you want to modify then click 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. Click the Columns page name 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. Click the Indexes page name 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. Click the Foreign Keys page name 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. Click the SQL View page name tab.

To view table data

  1. If the Grid window view is not displayed, click Window 4 Show View 4 Grid.
  2. Perform the steps for To start Table Editor for an existing table or To start Table Editor for a new table.
  3. By default, the Grid shows all of the data for the table (the result of a SELECT * FROM table statement).

    4. If the Grid is empty but the table contains data, right-click on any row in the Grid then click Refresh, or click .

    Note that the Grid allows you to directly change database data by changing the values in the grid cells. 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 4 Save or click .
    2. 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 4 Save All.
    2. 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 icon bar, click to undo an action; click to redo an action.

    2. If multiple actions have occurred since the last save, you can repeatedly click the undo or redo icon. When no more actions are available for undo or redo, the icon 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 required, perform the steps for To work with columns .
  2. Right-click on an existing column row above which you want to insert a new column.
  3. Click Insert Column.

    4. 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 column1 and so forth.)

Tip
You can also insert a column by clicking on an existing column row, then pressing Ctrl+Insert or clicking . Repeating either action inserts a series of columns in succession.
  1. Click File 4 Save or before changing pages within Table Editor.

To insert a column at the end

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for To work with columns .
  2. Right-click anywhere on the empty column row below the last existing column row, then click Add Column.

    3. or

    Click in 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.
  1. Click File 4 Save or before changing pages within Table Editor.

To select a column or multiple columns

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for To work with columns .
  2. Click (the column selection icon on the far left of the column row) for the desired column.

    3. To select multiple columns, press and hold Shift or Ctrl then click for the desired additional columns.

To delete a column

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for 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 on 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 .

  1. Click File 4 Save or before changing pages within Table Editor.

To specify a column name

See also Naming Conventions in Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for 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.

    5. The name can be alpha-numeric, 20 bytes or less. The name can have spaces, but spaces in column names are discouraged. Also, as a general rule, avoid using reserved words for column names. See Reserved Words in SQL Engine Reference.

  5. Click File 4 Save or before changing pages within 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 Pervasive PSQL 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 required, perform the steps for 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 4 Save or before changing pages within Table Editor.

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:

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 required, perform the steps for To work with columns .
  2. Click in the Precision cell for the desired column.

    3. 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 the Precision.

  3. Delete the existing value.
  4. Type the precision value you want.
  5. Click File 4 Save or before changing pages within Table Editor.

To set a column scale

Scale specifies the number of significant digits that are to the right of the decimal point for floating point values.

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for To work with columns .
  2. Click in the Scale cell for the desired column.

    3. 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 the Scale.

  3. Delete the existing value.
  4. Type the scale value you want.
  5. Click File 4 Save or before changing pages within Table Editor.

To set a column to allow or disallow nulls

For additional information about nulls, see Rebuild Utility Concepts in Advanced Operations Guide, INSERT in SQL Engine Reference, and Null Value in the Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for To work with columns .
  2. Click the option box in the Null cell for the desired column.

    3. 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 4 Save or before changing pages within 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 use an ACS.

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for To work with columns .
  2. Click the option box in the Case cell for the desired column.

    3. 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, Pervasive PSQL 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 4 Save or before changing pages within Table Editor.

To set a column collating sequence

For additional information about collating sequences, see Manipulating Btrieve Data Files with Maintenance in Advanced Operations Guide and Alternate Collating Sequences in the Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

If you use an alternate collating sequence (ACS), you cannot specify case sensitivity. Case sensitivity does not apply if the key uses an ACS.

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for To work with columns .
  2. Click in the Collate cell for the desired column.

    3. You can set an alternating collating sequence (ACS) 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. Type the path and ACS file name you want.

    5. Pervasive PSQL supplies an ACS file, upper.alt, in the Samples folder. To use this file, you would type c:\pvsw\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 4 Save or before changing pages within Table Editor.

To set a column default

The default value is used if you perform an SQL INSERT for a row but do not provide a value for the column.

  1. Ensure that the "Columns" page of Table Editor is active. If required, perform the steps for To work with columns .
  2. Click in the Default cell for the desired column.

    3. You can set a default only for applicable data types. If a default is not applicable, the grid cell is shaded and you will be unable to edit the Default.

  3. Delete the existing value, if present.
  4. Type the default value you want.

    5. The column default can be a scalar function for certain data types:

    Data Type
    Scalar Function1
    Date
    • now()
    • curdate()
    See also NOW ( ) and CURDATE ( ) , both in SQL Engine Reference.
    Time
    • now()
    • curtime()
    See also NOW ( ) and CURTIME ( ) in SQL Engine Reference.
    Timestamp
    • now()
    See also NOW ( ) in SQL Engine Reference.
    1 The names are case insensitive. NOW() and now() are equivalent. The parentheses are required. That is, NOW is invalid but NOW() is valid.

  5. Click File 4 Save or before changing pages within Table Editor.

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 required, perform the steps for To work with columns .
  2. Click (the column selection icon on the far left of the column row) for the desired column(s).

    3. To select multiple columns, press and hold Shift or Ctrl then click for the desired additional columns.

  3. Click (the primary key icon).

    4. If the column(s) is not a primary key, the action sets the column(s) 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 4 Save or before changing pages within Table Editor.

Index Tasks

To insert an index

Only the database management system (DBMS) can add an index to an IDENTITY or SMALLIDENTITY column. However, you can include an IDENTITY or SMALLIDENTITY column as part of a multiple-segment index.

Table Editor permits you to include an IDENTITY or a SMALLIDENTITY column in the Indexes list if you have not saved the table. However, the DBMS returns an error when you attempt to save the table. After you delete the IDENTITY or SMALLIDENTITY column from the list, you may then save the table.

  1. Ensure that the "Indexes" page of Table Editor is active. If required, perform the steps for To work with indexes .
  2. Click Add.
  3. Type the name that you want for the new index.

    4. The name is restricted to a maximum length of 20 bytes.

    Note that a name used for an index cannot be used for a foreign key, and vice versa.

  4. Click OK.

    5. The new index appears in the Indexes list.

    See also Creating Indexes in Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

  5. Click File 4 Save or before changing pages within Table Editor.

To modify an existing index

The database management system (DBMS) creates some indexes, such as IDENTITY column indexes and primary key indexes. These indexes are read-only and cannot be modified.

Table Editor permits you to include an IDENTITY or a SMALLIDENTITY column in the Indexes list if you have not saved the table. However, the DBMS returns an error when you attempt to save the table. After you delete the IDENTITY or SMALLIDENTITY column from the list, you may then save the table.

  1. Ensure that the "Indexes" page of Table Editor is active. If required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. Modify the segment details as desired.
  4. Click File 4 Save or before changing pages within Table Editor.

To delete an index

The database management system (DBMS) creates some indexes, such as IDENTITY 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 required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.
  3. Click the Delete button that applies to the Indexes list.



  4. Click Yes to confirm the deletion.
  5. Click File 4 Save or before changing pages within Table Editor.

To insert an index segment

For detailed information about segments, see Segmentation in the Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

Only the database management system (DBMS) can add an index to an IDENTITY or a SMALLIDENTITY column. However, you can include an IDENTITY or a SMALLIDENTITY column as part of a multiple-segment index. See also AUTOINC in SQL Engine Reference.

  1. Ensure that the "Indexes" page of Table Editor is active. If required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. Click in the first empty Columns cell, then open the list of columns (click ).



  4. From the list, click the desired column for the segment.

    5. 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 prefer descending, click in the "Sort Order" column then click Descending in the sort list.

    Note that a column can be selected as an index only once. Once selected, the column is removed from the list of choices. For example, suppose in the image above that you were to click on "Log" as an index column. You would have two index segments: "Student_ID" and "Log." If you wanted to add a third index segment, "Student_ID" and "Log" do not appear in the list of possible columns because they have already been designated as indexes.

  5. Click File 4 Save or before changing pages within Table Editor.

To modify an index segment

For detailed information about segments, see Segmentation in the Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

Only the database management system (DBMS) can add an index to an IDENTITY or a SMALLIDENTITY column. However, you can include an IDENTITY or a SMALLIDENTITY column as part of a multiple-segment index. See also AUTOINC in SQL Engine Reference.

  1. Ensure that the "Indexes" page of Table Editor is active. If required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. Click on the desired segment in the Columns cell then open the list of columns (click ).



  4. From the list, click the desired column for the segment.

    5. 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 prefer descending, click in the "Sort Order" column then click Descending in the sort list.

    Note that a column can be selected as an index only once. Once selected, the column is removed from the list of choices. For example, suppose in the image above that you were to click on "Log" as an index column. You would have two index segments: "Student_ID" and "Log." If you wanted a third index segment, "Student_ID" and "Log" do not appear in the list of possible columns because they have already been designated as indexes.

  5. Click File 4 Save or before changing pages within 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 required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. Click on the desired index segment.
  4. Click the Delete button that applies to the Index Segment Details.
  5. Click File 4 Save or before changing pages within Table Editor.

To arrange the order of index segments

  1. Ensure that the "Indexes" page of Table Editor is active. If required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. Click on the desired index segment.
  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 4 Save or before changing pages within Table Editor.

To specify a sort order for an index

For detailed information about sort order, see Sort Order in the Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

  1. Ensure that the "Indexes" page of Table Editor is active. If required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. Click in the Sort Columns cell then open the list of sort choices (click ).
  4. From the list, click Ascending or Descending.

    5. The default sort order when an index segment is created is ascending.

  5. Click File 4 Save or before changing pages within Table Editor.

To allow duplicates in an index

For detailed information about duplicates, see Duplicatability in the Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

  1. Ensure that the "Indexes" page of Table Editor is active. If required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. Click Allow Duplicates option.

    4. A check mark in the box indicates that the index allows duplicates (the values are not unique).

    Lack of a check mark indicates that the index values are unique (duplicate values are not allowed).

    Option State
    Meaning

    Duplicates allowed

    Duplicates not allowed

    Duplicates do not apply

    The default for all SQL data types is that the index column allows duplicates.

  4. Click File 4 Save or before changing pages within Table Editor.

To specify index as modifiable

For detailed information about modifiability, see Modifiability in the Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL Software Developer's Kit (SDK).

  1. Ensure that the "Indexes" page of Table Editor is active. If required, perform the steps for To work with indexes .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. Click Allow Modifications option.

    4. 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 4 Save or before changing pages within 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 required, perform the steps for To work with foreign keys .
  2. Click Add.
  3. Type the name that you want for the new foreign key.

    4. The name is restricted to a maximum length of 20 bytes.

    Note that a name used for a foreign key cannot be used for an index, and vice versa.

  4. Click OK.

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



  6. Click the desired table in the list (only tables with a primary key appear in the list).

    7. The primary field(s) in the table appear in "Primary Table Fields" column.



  7. Match fields in the foreign table with fields in the primary table:

    8. Click the empty cell in the "Foreign Table Fields" column for the corresponding field in the "Primary Table Fields," then click to displays 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.

    11. Pervasive PSQL 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 4 Save or before changing pages within Table Editor.

To modify a foreign key

  1. Ensure that the "Foreign Keys" page of Table Editor is active. If required, perform the steps for To work with foreign keys .
  2. Click on the desired index in the Indexes list.

    3. The Index Segment Details displays.

  3. As desired, select the primary table, match foreign table fields to primary table fields, and set the referential integrity rule.

    4. See steps 5 through 10 in To add a foreign key .

  4. Click File 4 Save or before changing pages within Table Editor.

To delete a foreign key

  1. Ensure that the "Foreign Keys" page of Table Editor is active. If required, perform the steps for To work with foreign keys .
  2. Click on the desired foreign key in the Foreign Keys list.
  3. Click Delete.
  4. Click Yes to confirm the deletion.
  5. Click File 4 Save or before changing pages within Table Editor.

SQL View Tasks

To copy SQL statements

  1. Ensure that the "SQL View" page of Table Editor is active. If required, perform the steps for To work with foreign keys .
  2. Position the cursor in the desired statement view: CREATE Statement or ALTER Statement.
  3. With the mouse, select the desired text. (Press and hold the right mouse button then "drag" across the desired lines.)
Tip
You can press Ctrl+A to select all of the text.
  1. 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 required, perform the steps for To work with foreign keys .
  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.

Chapter contents
Book contents

Prev topic: GUI Visual Reference
Next topic: SQL Editor