|
Step 8 - Creating Table in Oracle
|
Learn Oracle - Creating Tables
Oracle DBA can use Enterprise Manager to create tables. Oracle DBA can do so as a system
administrator or user with the CONNECT role.
Types of Table
The most common type of table in an Oracle database is a relational table, structured
like the employee table described at the beginning of this section, "Managing Tables"
Two other types of table are supported: object tables and XMLType tables.
Any of the three table types can be defined as permanent or temporary, the latter with
the option of making the table structure available to multiple users or only to the user
who creates the table.
Relational tables can be built in either heap or index organized structures. In the
former, the rows are not stored in any particular order but in index organized tables,
the row order is determined by the values in one of more columns which Oracle DBA select.
This chapter concentrates on permanent, heap organized tables. For information about
other table types and when to use them, see the Oracle Database Administrator's Guide,
Oracle Database Concepts, and the Oracle Database Performance Tuning Guide, and for the
syntax required to create and alter them, see the Oracle Database SQL Reference.
Column Attributes
Each column in a table is defined with one or more characteristics as follows:
Data type: a required element which defines the nature of the data to be stored in
the column and may a value to indicate the longest value that can be place in the
column.
Constraint: one or more optional elements that determine what values are valid in
the column. Some constraints can be defined at the column level or at the table
level. In Enterprise Manager, the only constraint defined at the column level on the
Create Table property page is the Not Null constraint, which requires that a value
is included in this column for every row in the table.
Default value: A value that is automatically stored into the column whenever a
new row is inserted without a value being provided for the column.
These column characteristics are described in more detail in the following sections.
Data Types When Oracle DBA create a table, Oracle DBA must specify a datatype for each of its
columns. When Oracle DBA create a procedure or stored function, Oracle DBA must specify a datatype
for each of its arguments.
These datatypes define the domain of values that each column can contain or each
argument can have. For example, DATE columns cannot accept the value February 29
(except for a leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a
column assumes the column’s datatype. For example, if Oracle DBA insert '17-JAN-2004' into a
date column, then Oracle treats the '17-JAN-2004' character string as a DATE value
after verifying that it translates to a valid date.
In most tables, Oracle DBA should only need columns of NUMBER, VARCHAR2, and DATE
datatypes. When defining numeric data, Oracle DBA may want to use the precision option, to
set the maximum number of digits in the number, and the scale option, to define how
many of them are to the right of the decimal point.
For example, a field to hold monetary values might be defined as
NUMBER(12,2)providing ten digits for the primary unit of currency (dollars, pounds,
marks, and so on), and two digits for the secondary unit (cents, pennies, pfenigs, and
so on). To define a VARCHAR2 field for character data, Oracle DBA must include the size value.
Set the size to the maximum number of bytes (or, optionally, characters) to be stored in
the column. A column to hold postal codes for different countries, for example, might
be restricted to 12 bytes by defining it as VARCHAR2(12). DATE columns are
automatically formatted by Oracle to include a date and time component.
Not Null Column Constraint The Not Null constraint on a column requires that column
must contain a value whenever a row is inserted or updated. Unlike other constraints,
described in "Table Level Constraints" on page 8-8, which may be defined as part of
the column definition or part of the table definition, the Not Null constraint must be
defined as part of the column definition.
Use a Not Null constraint when the data is required for the integrity of the database.
For example, if all employees must belong to a specific department, then the column
that contains the department identifier should be defined with a Not Null constraint.
On the other hand, do not define a column as Not Null if the data may be unknown or
may not exist when rows are added or changed, for example, the second, optional line
in a mailing address.
A primary key constraint automatically adds a Not Null constraint to the column or
columns included in the primary key.
Default Values When Oracle DBA define a column with a default value, any new rows inserted
into the table will store the default value unless the row contains an alternate value for
the column.
Table Level Constraints
In an Oracle database, Oracle DBA can apply rules to preserve the integrity of your data. For
example, in a table containing employee data, the name column cannot accept NULL
as a value. Similarly, in this table Oracle DBA cannot have two employees with the same ID.
Oracle enables Oracle DBA to apply data integrity rules called constraints to tables at the
column level or, with the exception of the Not Null constraint discussed earlier, at the
table level. Any attempt to insert or update a row that violates a constraint results in
an error and the statement is rolled back. Likewise any attempt to apply a new
constraint to a populated table will also result in an error if any existing row violates
the new constraint.
The types of constraints you can apply at the table level are as follows:
Primary Key---Requires that a column (or combination of columns) be the unique
identifier of the row. A primary key column does not allow NULLs.
Unique Key--Requires that every value in a column be unique. That is, no two rows
can have duplicate values in a specified column or combination of columns. The
set of columns is said to be the unique key.
Check--Requires that a column (or combination of columns) satisfy a condition for
every row in the table. A check constraint must be a boolean expression that is
evaluated using the column value about to be inserted or updated to the row.
Foreign---Requires that all column values in the child table exist in the parent table.
The table that includes the foreign key is called the dependent or child table. The
table that is referenced is called the parent table. An example of a foreign key
constraint is when the department column of the employees table (child) must
contain a department id that exists in the parent department table.
Constraints can be created and, in most cases, modified with a number of different
status values. The options include enabled or disabled, which determine if the
constraint is checked when rows are added or modified, and deferred or immediate,
Table Creation Example
In the following example, we create a table called purchase_orders in the MYUSER
schema . The table will
have columns po_number, po_description, po_date, and po_item.
To create a table in MYUSER’s schema, log in to Oracle Enterprise Manager as either
SYS or MYUSER and follow these steps:
1. Under the Administration page, click Tables. The Tables page appears listing the
tables in the MYUSER schema. Note if you logged in as SYS, Oracle DBA need to click the
flashlight and select MYUSER to manage tables in this schema.
2. Click Create. The Create Table: Table Organization page appears.
3. Select the box marked Standard, Heap Organized. Click Continue.
4. The Create Table page appears.
5. In the Name field, enter purchase_orders as the table name. In the Schema field,
accept the MYUSER schema, and in the Tablespace field, accept .
6. In the Columns section, enter the following columns with the datatypes:
po_id as NUMBER
po_date as DATE
po_desc as VARCHAR2(200)
po_item as VARCHAR2(100)
Oracle DBA can leave Scale, Not NULL, and Default Value blank.
7. Click OK. An update message appears indicating the table has been successfully
created
|
|
|
