Managing Tables

Creating a Table

To create a new table:

1. Navigate to Object Browser:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Table.

4. Enter a table name.

   Table names must conform to Oracle naming conventions and not contain spaces or start with a number or underscore.

5. To have the final table name match the case entered in the Table Name field, click Preserve Case.

6. Enter details for each column. For each column:

   1. Enter the column name.

   2. Select the column type. Available types include NUMBER, VARCHAR2, DATE, TIMESTAMP, CHAR, CLOB, BLOB, NVARCHAR2, BINARY_FLOAT, and BINARY_DOUBLE

   3. Enter the following additional information as appropriate:

      • Precision

      • Scale

   4. To specify a column should not be NULL, select the check box in the Not Null column.

   To change the order of previously entered columns, click the Up and Down arrows in the Move column. To add additional columns, click Add Column.

   Next, define the primary key for this table (optional). A primary key is a single field or combination of fields that uniquely identifies a record.

7. For Primary Key, select one of the following and click Next:

   • No primary key - No primary key is created.

   • Generated from a new sequence - Creates a primary key and creates a trigger and a new sequence. The new sequence is used in the trigger to populated the selected primary key column. The primary key can only be a single column.

   • Generated from an existing sequence - Creates a primary key and creates a trigger. The selected sequence is used in the trigger to populate the selected primary key column. The primary key can only be a single column.

   • Not generated - Defines a primary key but does not have the value automatically populated with a sequence within a trigger. You can also select this option to define a composite primary key (that is, a primary key made up of more than one column).

   Next, add foreign keys (optional). A foreign key establishes a relationship between a column (or columns) in one table and a primary or unique key in another table.

8. To add a foreign key:

   1. Name - Enter a name of the foreign key constraint that you are defining.

   2. Select Key Column(s) - Select the columns that are part of the foreign key. Once selected, click the Add icon to move them to Key Column(s).

   3. References Table - Select the table which will be referenced by this foreign key. Then, select the columns to be referenced by this foreign key. Once selected, click the Add icon to move the selected columns to Referenced Column(s).

   4. Select one of the following:

      • Disallow Delete - Blocks the delete of rows from the referenced table when there are dependent rows in this table.

      • Select Cascade Delete - Deletes the dependent rows from this table when the corresponding parent table row is deleted.

      • Set to Null on Delete - Sets the foreign key column values in this table to null when the corresponding parent table row is deleted.

   5. Click Add.

   6. Click Next.

   Next, add a constraint (optional). You can create multiple constraints, but you must add each constraint separately.

9. To add a constraint:

   1. Specify the type of constraint (Check or Unique).

      A check constraint is a validation check on one or more columns within the table. No records can be inserted or updated in a table which violates an enabled check constraint. A unique constraint designates a column or a combination of columns as a unique key. To satisfy a unique constraint, no two rows in the table can have the same values for the specified columns.

   2. Enter the constraint in the field provided. For unique constraints, select the column(s) that are to be unique. For check constraints, enter the expression that should be checked such as, flag in ('Y','N').

   3. Click Add.

10. Click Finish.

    A confirmation page appears. To view the SQL used to create the table, click SQL Syntax.

11. Click Create.

    Note that you do not need to follow the steps for creating a table in the order described in the previous procedure. Instead of navigating through the wizard by clicking the Next and Previous button, you can also access a specific step by selecting it in the progress indicator on the left side of the page.

Browsing a Table

When you view a table in Object Browser, the table description appears. While viewing this description, you can add a column, modify a column, rename a column, drop a column, rename the table, copy the table, drop the table, truncate the table, or create a lookup table based upon a column in the current table. Additionally, you have access other reports that offer related information including the table data, indexes, data model, constraints, grants, statistics, user interface defaults, triggers, dependencies, and SQL to produce the selected table.

To view a table description:

1. Navigate to Object Browser:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Tables.

3. From the Object Selection pane, select a table.

   The table description appears.

Editing a Table

While viewing a table description, you can edit it by clicking the buttons above the table description.

To edit a table:

1. Navigate to Object Browser:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Tables.

3. From the Object Selection pane, select a table.

   The table description appears.

4. Click the appropriate button described in Table 18-2.

   Table 18-2 Edit Table Buttons

   Button	Description
   Add Column	Adds a new column to the table. Enter a column name and select a type. Depending upon the column type, specify whether the column requires a value as well as the column length, precision, and scale.
   Modify Column	Modifies the selected column.
   Rename Column	Renames the selected column.
   Drop Column	Drops the selected column.
   Rename	Renames the selected table.
   Copy	Copies the selected table.
   Drop	Drops the selected table. See Also: "Using the Recycle Bin to View and Restore Dropped Objects"
   Truncate	Removes all rows from the selected table. Truncating a table can be more efficient than dropping and re-creating a table. Dropping and re-creating a table may invalidate dependent objects, requiring you to regrant object privileges or re-create indexes, integrity constraints, and triggers.
   Create Lookup Table	Creates a lookup table based on the column you select. That column becomes a foreign key to the lookup table.