|
Step 8 - Modifying Table Attributes
|
Learn Oracle - Modifying Table Attributes
Oracle DBA can use Enterprise Manager to modify tables, such as adding and deleting
columns or adding constraints. The following sections show how to add and delete a
column, and how to add, change the status of, and drop a table constraint.
Adding a Column
In this example, we modify the PURCHASE_ORDERS table by adding a new column
called po_item2. To add the new column:
1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table
in the results list and click Edit.
2. In the Edit Table page, click Add 5 Table Columns. An editable columns list
appears.
3. Enter po_item2 as VARCHAR2(100) as the new column. Oracle DBA can leave the Scale,
Not Null, and Default Value blank. Click Apply.
4. An Update Message appears indicating that the table has been modified
successfully.
Dropping a Column
This example shows Oracle DBA how to drop column, in this case, the po_item2 column that
was added to the PURCHASE_ORDERS table in the previous section ("Adding a
Column"). To delete the column:
1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table
in the results list and click Edit.
2. In the Columns table on the Edit Table page, select the column Oracle DBA want to delete,
po_item2.
3. Click Delete.
4. The row for that contained the information for the deleted row, po_item2, becomes
blank to indicate that the table has been modified successfully.
Creating a New Table Constraint
Oracle DBA can add constraints to a table with Enterprise Manager. In our current example,
we want to enforce that our purchase orders always be entered with an ID and that
this column (po_id) be the primary key of the table. We will add the not null and
primary key constraints to this column.
To add these constraints to the purchase_orders table, do the following:
1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table
in the results list and click Edit.
2. To apply the Not Null constraint, check the Not Null box next to the po_id
column. Click Apply.
3. To apply the primary key constraint, click the Constraints property page.
4. On the Constraints page, select PRIMARY from the drop-down menu, and click
Add. The Add PRIMARY Constraint page appears.
5. In the available columns list, select po_id and move it to the selected columns list.
Do not change the check boxes in the Attributes area of the page because so that
the default settings for the constraint are retained.
6. Click OK. On the Edit Table page, click Apply. A confirmation message appears.
Oracle DBA can view the new constraints by navigating to the View Table page.
Modifying an Existing Constraint
Oracle DBA can change the status of an existing constraint, for example, from an enabled to a
disabled state. Using the primary key constraint created in the previous section,
"Creating a New Table Constraint", here is an example of how to disable a constraint:
1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table
in the results list and click Edit.
2. To locate the constraint to be disabled, click the Constraints property page.
3. On the Constraints page, select the row containing the PRIMARY constraint from
the Constraints table, and click Edit.
4. Under the Attributes section of Edit PRIMARY Constraint window, select the
Disabled box.
5. Click OK. On the Edit table page, click Apply. A confirmation message appears
and the value in the Disabled column for the primary key constraint should be
Yes.
Dropping a Constraint
Oracle DBA can drop constraints to a table with Enterprise Manager. Although Oracle DBA do not
have to disable a constraint before dropping it, Oracle DBA can determine whether the
constraint can be dropped by attempting to disable it first. When a constraint in a
parent table is used to enforce a foreign key constraint in a child table, the constraint
cannot always be disabled or dropped if the child table contains dependent rows.
Continuing the current example, we drop the primary key constraint that we created
earlier in the section, "Creating a New Table Constraint", by completing the following
steps:
1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table
in the results list and click Edit.
2. To locate the constraint to be disabled, click the Constraints property page.
3. On the Constraints page, select the row containing the PRIMARY constraint from
the Constraints table, and click Delete.
4. On the Edit table page, click Apply. A confirmation message appears.
|
|
|
