Creating a Table

To create a table:

1. On the Workspace home page, click SQL Workshop and then 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.

   3. Enter the following additional information as appropriate:

      • Precision

        The precision is the number of digits before the decimal point. Not all column types have this setting. Precision must be a positive integer. Only NUMBER, FLOAT, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND have this setting. For example, if a column of type NUMBER has precision of 4, the column value can be 0 through 9999.

      • Scale

        The function of the scale setting depends on the column type. For NUMBER types, scale is the number of digits after the decimal point. For VARCHAR2 and CHAR types, scale is the number of stored characters and must be no more than 4000 bytes. For TIMESTAMP types, scale is the fractional seconds precision and must be a number between 0 and 9. Scale must be a positive number. For example, if a column type TIMESTAMP has a scale of 3, seconds are between .000 seconds and .999 seconds.

   4. To specify a column must always have a value, 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.

   5. Click Next.

   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 the appropriate option and click Next:

   • No Primary Key - No primary key is created.

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

   • Populated 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 populated - 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 multiple columns).

   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, then click the Add icon to move them to Key Column(s).

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

   4. Select the appropriate option:

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

      • 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 Next.

    A confirmation page appears.

11. Click Create.

    Note that it is not necessary 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.