Creating and modifying tables

Learning objective

After completing this topic, you should be able to recognize how to create and modify tables in an Oracle 10g database.

1. Viewing table contents

Tables are the basic units of data storage in an Oracle database. They have columns and rows containing all user-accessible data.

You can use Enterprise Manager to view the attributes of a table. To do this, you navigate to the Administration tabbed page.

In the Schema section, you select Tables.

Suppose you want to search for the tables under the Human Resources schema.

If you know the schema name, you can enter all or part of it in the Schema field in the Search region.

Alternatively, if you don't know the schema name, you can click the flashlight icon next to the Schema field to bring up a list of schemas.

Here you type HR in the schema section, and click Go.

The Results page lists all the tables in the HR schema.

You want to view the attributes of the EMPLOYEES table.

You select the option button next to the EMPLOYEES table. Now you want to look at the table attributes.

You click View.

The View Table: HR.EMPLOYEES page appears.

You scroll down to view the columns. The attributes you can view here are column name, data type, and size, which are all mandatory.

You can also view a column's scale, whether it is designated as NOT NULL, and its default value.

You scroll down again to see the constraints associated with the table. These are used to ensure that valid data is stored in your database tables.

After viewing the EMPLOYEES table, you click OK.

This returns you to the Tables page.

You scroll down to the list of tables from the HR schema.

In the Results section, you can view the actual contents of a table by using the Actions drop-down list. Here you have selected the COUNTRIES table to view.

You select View Data from the Actions drop-down list, and click Go.

In the View Data For Table page, the Query box displays the SQL query that was executed to produce the results.

The contents of the table is shown in the Result section.

On this page, you can click any column name and sort the data in the column in either ascending or descending order.

For example, you can sort the table in descending order of region ID by clicking the REGION_ID column.

To change the query, you scroll to the top of the page.

Here you click Refine Query.

On the Refine Query for Table page, you can select the columns that you want to display.

You can also specify a WHERE clause for the SQL statement to limit the results.

Suppose you want to remove the COUNTRY_ID column.

You select it and click Remove.

The COUNTRY_ID column is then placed in the Available Columns list box.

Then you click OK.

This returns you to the View Data for Table: HR.COUNTRIES page.

The values for the COUNTRY_ID column are no longer included in the query results output.

Question

What information can you see when viewing the attributes of a table?

Options:

Column name

Data type

Constraints

Row data

Answer

Some of the attributes you can see when viewing a table are column names, data types, and constraints.

Option 1 is correct. The column name is mandatory.

Option 2 is correct. The column's data type is mandatory.

Option 3 is correct. You can set constraints to ensure that valid data is stored in your database tables.

Option 4 is incorrect. To view row data, you choose the table you want to view, and then select View Data from the Actions drop-down list.

Question

Suppose you want to view the attributes of the Human Resources table called DEPARTMENTS. You have already navigated to the Tables page and run a search for all HR schemas.

This question requires you to view the attributes of the DEPARTMENTS table. Which option enables you to do this?

Options:

In the Results section, you click View.

In the Results section, you select the DEPARTMENTS option and click View.

Answer

In the Results section, you select the DEPARTMENTS option and click View.

2. Creating tables

You can create a table using the Tables page in Enterprise Manager.

Suppose you want to create a table for payroll.

You click Create in the Results section.

In the Create Table: Table Organization page, you specify how you want Oracle to store the table - Standard, Heap Organized or Index Organized Table.

You accept the default Standard, Heap Organized option by clicking Continue.

In the Create Table page, you must enter the table name and optionally specify a schema and tablespace.

You type PAYROLL in the Name field.

To choose a schema, you can either use the flashlight icon to invoke the search function or enter the schema name in the Schema field.

Because you want to create the table in the Human Resources schema, you enter HR in the Schema field.

You accept the default tablespace.

You scroll down to the Columns section, where you want to define the first column.

You type EMPLOYEE_ID in the Name field, and then click the Size text field.

You add the size and the data type for the column.

You choose the NUMBER data type, and enter a size of 6.

You enter four more column names - FIRST_NAME, LAST_NAME, TOTAL_PAY, and NET_PAY.

And you select appropriate data types and sizes for the columns.

Then you click OK to accept your settings.

An update message appears, indicating that the table has been created successfully.

Question

Which of these steps are involved in creating a database table?

Options:

Entering a table name, schema, and tablespace

Entering a column name, data type, and size

Opening the Create page

Opening the Edit Table page

Specifying a table organization option

Answer

To create a table, you must open the Create page, select a table organization option, and specify the table name, schema, and tablespace. Then you must enter a column name, data type, and size.

Option 1 is correct. You enter this information on the Create table page.

Option 2 is correct. You must enter at least one column, specifying its data type and size.

Option 3 is correct. To do this, you click the Create button in the Results section of the Tables page.

Option 4 is incorrect. You use the Edit Table page to modify existing tables.

Option 5 is correct. You can have Oracle store your table using the Standard, Heap Organized option or the Index Organized Table option.

You can also modify a table using Enterprise Manager. Suppose you want to edit the PAYROLL table you've just created.

To do this, you first return to the Tables page.

You search the HR schema to list the tables it contains.

In the Results section, you need to choose the table to modify, and then open the Edit Table page.

You select the PAYROLL option, and click Edit.

This brings you to the Edit Table: HR.PAYROLL page.

Here you want to change the size of the EMPLOYEE_ID number.

You scroll to the Columns section.

In the EMPLOYEE_ID row, you change the size to 12, and you click Apply.

An update message appears, indicating that the table has been modified successfully.

Question

Suppose you want to create a table called RECRUITMENT in the HR schema. You have already navigated to the Tables page in Enterprise Manager.

This question requires you to create a table called RECRUITMENT in the HR schema. You must accept the default table organization and tablespace and, in the first field available, create a column called POSITION, with VARCHAR2 as the data type, and25 as the size. Which option enables you to do this?

Options:

In the Results section, you click Create. Next you click Continue in the Create Table: Table Organization page. Then you type RECRUITMENT in the Name field of the Create Table page. You type POSITION in the first column name and you type 25 in the first Size field. Finally, you click OK.

In the Results section, you click Create. Next you click Continue in the Create Table: Table Organization page. They you type POSITION in the Name field of the Create Table page. You type RECRUITMENT in the first column name and you type 8 in the first Size field. Finally, you click OK.

Answer

3. Understanding data integrity

You can use the following integrity constraints to impose restrictions on the input of column values.

NOT NULL

UNIQUE

PRIMARY KEY

Referential integrity constraints

CHECK constraints

NOT NULL: Null means the absence of a value. By default, all columns in a table allow null values.

A NOT NULL constraint requires that a table column have no null values.

For example, you might use a NOT NULL constraint to ensure that a customer ID is entered for every row of a sales table.
UNIQUE: A UNIQUE key integrity constraint requires that every value in a column or set of columns be unique. This means rows cannot have the same values in a specified column or set of columns.

For example, you can define a UNIQUE key constraint on the DEPARTMENT_NAME column of the DEPARTMENTS table to prohibit rows with duplicate department names.
PRIMARY KEY: Database tables can have at most one PRIMARY KEY constraint.

The values in the group of one or more columns subject to this constraint constitute the unique identifier of the row.

This means you are naming each row by its primary key values.

The Oracle implementation of the PRIMARY KEY integrity constraint ensures that no two rows of a table have duplicate values in the specified column or set of columns, and that the primary key columns do not allow nulls.

This means that the primary key columns in each row must have a value.

To enforce all PRIMARY KEY constraints, Oracle uses indexes.

For example, the primary key constraint of the DEPARTMENT_ID column in the DEPARTMENTS table is enforced by a unique index on that column and a NOT NULL constraint for that column.
Referential integrity constraints: You can relate different tables in a relational database by common columns. However, you must maintain the rules that govern the relationship of the columns.

A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.

As an example, a foreign key is defined on the DEPARTMENT_ID column of the EMPLOYEES table.

This guarantees that every value in this column must match a value in the primary key of the DEPARTMENTS table.

This means that only correct department numbers exist in the DEPARTMENT_ID column of the DEPARTMENTS table.

Another type of referential integrity constraint is the self-referential integrity constraint.

This type of foreign key references a parent key in the same table.
CHECK constraints: A CHECK integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every row of the table.

If a DML statement results in the condition of the CHECK constraint evaluating to false, then Oracle rolls back the statement.

Question

Match each integrity constraint to the correct description of how it controls the type of input allowed in a column.

Options:

NOT NULL

UNIQUE key

PRIMARY KEY

CHECK constraints

Targets:

Requires that a value be inputted in a column of a table

Requires that no two rows of a table have duplicate values in a column of a table

Ensures the values in the group of one or more columns constitute the unique identifier of the row

Requires that a specified condition be true or unknown for every row of the table

Answer

A NOT NULL constraint requires that a value be input in a column of a table. A UNIQUE Key constraint requires that no two rows of a table have duplicate values in any column. A PRIMARY KEY constraint ensures the values in the group of one or more columns constitute the unique identifier of the row. A CHECK constraint requires that a specified condition be true or unknown for every row of a table.

NULL means the absence of a value. By default, all columns in a table allow null values.

A UNIQUE key integrity constraint requires that every value in a column be unique.

Database tables can have at most one PRIMARY KEY constraint.

If a DML statement results in the condition of the CHECK constraint evaluating to false, then Oracle rolls back the statement.

Suppose you want to add a constraint to the PAYROLL table.

You first select the table on the Tables page, and then click Edit.

To add a constraint, you need to open the Constraints tabbed page.

You click Constraints.

The Constraints page displays all the constraints that have already been defined for the PAYROLL table. You want to create a UNIQUE constraint.

You select UNIQUE from the Constraints drop-down list and click Add.

In the Add UNIQUE Constraint page, you want to name the constraint you are about to create.

You enter UNIQUE_EMPLOYEE_ID in the Name field.

You now need to select the column to which you want to apply the constraint.

You select EMPLOYEE_ID as the UNIQUE key, and click Move.

You then click OK.

You return to the HR.PAYROLL page.

The UNIQUE constraint is added to the last row of the table.

Question

Suppose you want to specify a constraint for the PAYROLL table. You have navigated to the Edit Table HR.PAYROLL page.

This question requires you to add a PRIMARY key constraint to the EMPLOYEE_ID field. Which option enables you to do this?

Options:

In the Edit Table: HR.PAYROLL page, you click Constraints, and then click Add. In the Table Columns section of the Add PRIMARY Constraint page, you select EMPLOYEE_ID, and click Move. Finally, you click OK.

In the Edit Table: HR.PAYROLL page, you click Constraints. Then select PRIMARY from the Constraints drop-down list, and click Add. In the Table Columns section of the Add PRIMARY Constraint page, you select EMPLOYEE_ID, and click Move. Finally, you click OK.

Answer

In the Edit Table: HR.PAYROLL page, you click Constraints. Then select PRIMARY from the Constraints drop-down list, and click Add. In the Table Columns section of the Add PRIMARY Constraint page, you select EMPLOYEE_ID, and click Move. Finally, you click OK.

4. Performing actions on tables

You can use the Actions drop-down list on the Tables page to perform a wide range of actions on a selected table.

Create Like

Create Index

Create Synonym

Create Trigger

Gather Statistics

Generate DDL

Grant Privileges

Create Like: The Create Like action enables you to create a table with the same structure as the selected table. You must change the table name and the constraint names. You can add to, delete from, or change the table structure. The table is empty when created.
Create Index: The Create Index action enables you to create indexes on a table. You should create indexes only on columns used in WHERE conditions of SELECT and DML statements.
Create Synonym: The Create Synonym action enables you to create a synonym or another name for the full table name. A synonym can be private or public.
Create Trigger: The Create Trigger action enables you to create a table-level trigger by executing a PL/SQL block when an event occurs on the table.
Gather Statistics: The Gather Statistics Wizard enables you to generate and modify optimizer statistics. The data dictionary stores the statistics, and a cost-based optimizer uses them.
Generate DDL: The Generate DDL action generates the DDL command to create the table selected. You can then copy and paste the command into a text file to use as a script or documentation.
Grant Privileges: The Grant Privileges action enables you to let other users perform DML and some DDL on a table. By default, when a table is created, only the owner can work with it.

You can perform other actions on selected tables using the Actions drop-down list.

Reorganize

Run Segment Advisor

Shrink Segment

Show Dependencies

View Data

Flashback Table

Flashback by Row Versions

Reorganize: The Reorganize action enables you to rebuild fragmented indexes or tables, move objects to a different tablespace, and optimize storage for specified objects.
Run Segment Advisor: The Run Segment Advisor enables you to determine whether objects have unused space that can be released. The Advisor considers future space requirements by reference to historical trends.
Shrink Segment: The Shrink Segment action enables you to compact and free fragmented space.
Show Dependencies: The Show Dependencies action enables you to display the objects that the table depends on and the objects that depend on the table.
View Data: The View Data action enables you to view table data by running a SELECT statement on the table. You can refine the SELECT statement, but you cannot change the data.
Flashback Table: The Flashback Table action enables you to recover an earlier state of a table that has been accidentally modified or deleted. This occurs while the database is online, and involves rolling back only the changes to the given table.
Flashback by Row Versions: The Flashback by Row Versions action enables you to query metadata and historical data recorded within a specified time interval.

Question

Which of these actions can you perform on tables using the Actions drop-down list?

Options:

Create a table using another table as a template

Delete a table

Gather statistics

Grant privileges

Answer

Actions you can perform on tables using the Actions drop-down list include creating a table using another table as a template, gathering statistics, and granting privileges.

Option 1 is correct. The Create Like action enables you to create a table with the same structure as the selected table.

Option 2 is incorrect. To delete a table, you navigate to the Tables section in the Schema region of the Administration page, select the table, and click Delete.

Option 3 is correct. The Gather Statistics action enables you to generate and modify optimizer statistics.

Option 4 is correct. The Grant Privileges action enables you let other users perform DML and some DDL on a table.

Summary

Oracle 10g databases use the rows and columns of tables to store user-accessible content. You can use Enterprise Manager to view the attributes and contents of tables.

Enterprise Manager also enables you to create and modify tables. When building a table, you need to specify how Oracle stores it. You must also specify a table name, (schema and tablespace are optional), and enter at least one column name along with its data type and size.

You can impose restrictions on the input of column values by using integrity constraints such as NOT NULL, UNIQUE key, PRIMARY KEY, and CHECK constraints.

You can perform actions on selected tables using the Actions drop-down list. They include creating a table with the same structure as the selected table, creating indexes on a table, and creating a table-level trigger when an event occurs on the table.

Oracle 10g

Wednesday, September 5, 2007