Creating and Modifying Tables and Table Objects

Creating a Table

To add a table to a database:

  1. Select the database in the left panel.

    NOTE: For information about opening a database, see Opening an Existing Database. For information about creating a new database, see Creating a Database.

  2. Do one of the following:
  3. Type the table name in the Name box.

    See Case Sensitivity in Table and Column Names for information about how case is handled in new table names.

  4. Choose the Schema for the table. By default, the schema is APP.
  5. Begin adding and defining columns in the grid. For each column:
  6. After each column is defined, click the + button above the Columns grid to add another column. (Conversely, you can delete a selected column with the - button).
  7. Optionally, you can specify the lock level for the table (row- or table- level). Click the Properties tab to find and set the table lock level.
  8. When finished defining the table, click OK to create it.

NOTE: Unless you have unchecked the property Save Edits without Prompting in the System Settings (Property tab), new tables and columns are saved automatically when you click outside the table tab area. You can also explicitly save the table or column by clicking OK. See Saving New Tables and Columns Automatically for more details.

Column Specifications

Modifying an Existing Table

Once a table has been created, you can alter a subset of table elements from within Cview.

Adding Columns to an Existing Table

After you have created a table, you can add columns.

  1. Open the table by highlighting it in the left panel.
  2. In the right panel, click the + symbol to add a column.
  3. Specify the column information as described in Creating a Table.
  4. Repeat steps 2 and 3 for each column you want to add.
  5. When finished, click OK to save the modified table.

Modifying the Length of BIT VARYING or Character Type Columns

Once a table has been created, you can modify the length of BIT VARYING or selected character type columns (CHAR, NATIONAL CHAR, VARCHAR, and NATIONAL VARCHAR).

  1. Highlight the table name in the left panel.
  2. In the Table tab, select the length field for the column you want to modify.
  3. Type in the new length value and click OK.

Modifying the Nullability of a Column

Once a table has been created, you can modify columns defined as NOT NULL to accept NULLs.

  1. Highlight the table name in the left panel.
  2. In the Table tab, select a nullable field which contains No.
  3. Click the drop-down arrow and choose Yes.
  4. Repeat steps 2 and 3 for each column you want to change.
  5. Click OK to save changes.

Adding or Dropping an Autoincrement Default

You can modify an INT type column to include an autoincrement default, or to remove such a default. To add or remove an autoincrement default, toggle the Auto Increment check box for that column on the Table tab.

You can also modify the initial and increment values for an autoincrement column by editing the Initial Value and Increment fields.

For more information about the autoincrement default, see Column Specifications.

NOTE: You cannot import data into a table with an autoincrement default defined on one of its columns. Prior to importing data, you must drop the default by unchecking the Autoincrement column. After import, you can re-add the default.

Case Sensitivity in Table and Column Names

By default, table and column names are case-insensitive and are created as all uppercase. How to specify case sensitivity depends on whether you are explicitly executing DDL commands from the Cview SQL window, or whether you are creating objects using the Cview Edit Panel.

When explicitly executing DDL commands from the Cview SQL window, you can use quotation marks to indicate case-sensitivity. For more information, see Case Sensitivity in the SQL Window.

When creating objects from the Edit Panel you must set a system property to create case-sensitive object names.

Turning Off Case Sensitivity

To turn off case sensitivity:

  1. Choose System in the left panel.
  2. Choose the Preferences tab in the right panel.
  3. Uncheck Save DDL as case insensitive.

For information about creating case-sensitive table and column names from the SQL window, see Case Sensitivity in the SQL Window for details.

Creating Indexes

To create a new index:

  1. Do one of the following:
  2. Choose the Index tab if it is not already chosen.
  3. Enter the name of the index in the Name field.
  4. Choose Unique or Non-unique in the Type field.

    NOTE: Unique keys and unique indexes function the same way. The difference is in the SQL syntax used to create them: a unique index uses the CREATE UNIQUE INDEX statement, and a unique key uses the SQL standard ALTER TABLE ADD CONSTRAINT syntax.

  5. Choose the column(s) to include in the index from the Columns grid. (The way to select multiple columns depends on your operating system.) If the index has more than one column, verify the order shown in the Order column of the grid.
  6. Choose the Properties tab.
  7. To alter the default values, enter the page size and number of initial pages.

    NOTE: The index row must fit on the page, so make sure the page size is large enough.

  8. Choose the SQL tab to see the SQL statement for the index.
  9. Check the values you have entered in both the Index and the Properties tabs to make sure they are correct.
  10. Click OK.

Creating a Key

To create a key:

  1. Do one of the following:
  2. In the Key tab, enter the name of the key in the Name field.
  3. Choose a type for the key in the Type field. Keys can be unique, primary, or foreign.

    NOTE: Unique keys function the same way as unique indexes. The difference is in the SQL syntax used to create them: a unique index uses the CREATE UNIQUE INDEX statement, and a unique key uses the SQL standard ALTER TABLE ADD CONSTRAINT syntax.

  4. Choose the column(s) to include in the key from the Columns grid. (The way to select multiple columns depends on your operating system.) If the key has more than one column, verify the order shown in the Order column of the grid.
  5. Choose the SQL tab to see the SQL statement for the key.
  6. Click OK.

NEW: In version 5.2, you can use the On Delete and On Update fields when creating a foreign key, to specify the appropriate referential action. You can only specify referential actions when creating the foreign key; these fields are read-only when you are viewing an existing foreign key.

Creating a Check Constraint

To create a check constraint:

  1. In the left panel, highlight the constraint node, a specific constraint, or the name of the table on which to create a check constraint.
  2. Choose New->Check from the right-click menu, from the Edit menu, or by clicking the Check icon or New button in the right panel.

    The check constraint definition form displays.

  3. Choose the Check tab if it is not already chosen.
  4. Enter the name of the check constraint in the Name field.
  5. Enter the condition in the Condition field.

    You can use the Auto Text icon above the Condition field to help you write the condition. To choose a column from the table, click the Auto Text icon, choose Columns, then click the column you want. To add an operator or keyword, click the Auto Text icon, choose Key Words, then click the key word or symbol you want.

    You may have to type in the Condition field even if you use auto text. For example, to allow only values greater than 0 in the ID column, use auto text to choose the ID column and the ">" operator, then type 0 directly in the Condition field.

  6. Click OK.