Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 30

In Chapter 27, we gave you a whirlwind tour of Oracle Database XE administration, describing the logical and physical storage structures of a database, including tablespaces and the other structures
that comprise a database.
In this chapter, we approach database objects from both a user’s and a developer’s point of view, starting with an overview of how to create and manage tablespaces. Inevitably, you’ll grow out of the five default tablespaces included with an Oracle Database XE installation. The type of tablespace you create is influenced by the type of data you store in it, such as undo tablespaces, temporary tablespaces, and permanent tablespaces.
Next, we give you a rundown of all basic Oracle datatypes. For the most part we cover the key datatypes that fall into three broad categories: numeric, character, and date. In addition, we present many of the object-oriented, binary, and even XML types. To expand on the multilingual support in Oracle Database XE, we explain how Oracle supports Unicode in the database.
Finally, once you know the datatypes available for a column, we show you how to create several different types of tables using these datatypes. To improve the performance of table access, you’ll also need to know about table indexes and when you need to create them.
Knowing how to create the right types of tables with appropriate column types, indexes, and other characteristics ensures that your application uses database storage efficiently while at the same time making your application perform adequately for your user base.

Creating and Managing Tablespaces

As you learned in Chapter 27, a tablespace is the highest-level logical object in the database. A default installation of Oracle Database XE consists of five tablespaces: SYSTEM, SYSAUX, TEMP, USERS, and UNDO. Tablespaces typically store objects such as tables and indexes that share a common function or belong to a particular user application. Tablespaces are logical containers, comprising one or more physical files on disk that make it easy to map the storage of your tables and indexes onto physical disks as you please. For example, the SYSTEM tablespace contains, as you might expect, the database’s metadata, such as user accounts, table definitions, performance metrics, and so forth.
First, we’ll give you an overview of the tablespace types and how you use them in a typical database. The number and types of tablespaces in your database has a direct correlation to the performance and scalability of the applications your database supports. In addition, we’ll step through an example of creating a new tablespace to support your new Web- and PHP-based application.

Tablespace Types

The primary types of tablespaces in any Oracle database are permanent, undo, and temporary. As of Oracle Database 10g (which provides the code base for Oracle Database XE), you can use a special kind of permanent, undo, or temporary tablespace called a bigfile tablespace to ease administrative tasks.

513

Your database will predominantly consist of permanent tablespaces to accommodate your growing application data needs. Your undo and temporary tablespaces will grow as your applica- tions and number of users grow, but not as quickly.

Permanent

Permanent tablespaces contain database objects (such as tables and indexes) that are retained beyond the end of a user session or transaction (we’ll cover transactional rules and usage in Chapter 32). In your Oracle XE database, the SYSTEM, SYSAUX, and USERS tablespaces are examples of permanent tablespaces.
One of the permanent tablespaces in an installation of Oracle Database XE, SYSTEM, should never have any user or application tables. It is for database metadata such as user accounts, system statistics, and definitions for all tables and tablespaces across the database.
The companion tablespace to the SYSTEM tablespace, the SYSAUX tablespace, contains database objects for major database features such as the Enterprise Manager repository, Oracle Ultra Search, and Oracle Streams. While these are key features leveraged in many Oracle databases, they are not critical to the continued operation of the database. As a result, if the SYSAUX tablespace is damaged or you take it offline, the database continues to function.
Because of the amount of metadata managed by these additional Oracle features, the SYSAUX tablespace was added in Oracle Database 10g to keep the size of the SYSTEM tablespace more constant and easier to manage and optimize for the core Oracle features. If an Oracle feature uses an increasingly larger percentage of space in SYSAUX, you can create another permanent tablespace for a particular Oracle feature.
The USERS tablespace, as the name implies, is the default tablespace for nonsystem users in the database. Unless you specify otherwise, when you create a new database user the user’s tables, indexes, sequences, and so forth reside in the USERS tablespace.

Undo

You can create multiple undo tablespaces in your database, but only one undo tablespace can be active at any one time. The database uses an undo tablespace to roll back failed or uncommitted transactions as well as to provide read consistency for SELECT statements that may occur simulta- neously with data manipulation language (DML) statements such as DELETE, INSERT, and UPDATE. In other words, undo tablespaces store the previous values of columns being updated or deleted, and therefore provide a SELECT statement a view of the table that is consistent until the SELECT statement completes, even though INSERT, DELETE, and UPDATE activity may be occurring on any or all rows of the table.
While the monitoring and sizing of undo tablespaces is beyond the scope of this book, you want
to make the size of the undo tablespace large enough to support your longest running SELECT state- ments during DML activity, but not so large as to use disk space that can be used for other applications, Oracle or otherwise.

Temporary

Temporary tablespaces contain transient data that exists only for the duration of a user transaction or session, such as data to support a sort operation that will not fit in memory. You cannot save perma- nent objects in a temporary tablespace.

Bigfile

You can use a bigfile tablespace for permanent, undo, or temporary tablespaces. A bigfile tablespace consists of a single datafile up to a size of 131,072GB, and if you use a database block size of 32K, your

database can have a total size up to 8 exabytes (8EB). Its primary advantage is for ease of mainte- nance; commands you use to maintain datafiles within a tablespace are now available at the logical tablespace level.
Even though Oracle Database XE restricts the total database size to 4GB, you can still create
bigfile tablespaces to take advantage of their manageability features.

■Note Other databases, such as MySQL, provide table performance, scalability, and availability benefits by using
different tablespace types or storage engines; Oracle uses a single tablespace type for permanent tables and allows for different table types within the same tablespace. This simplifies tablespace management and permits tables and indexes with as variety of access methods to coexist within a single tablespace.

Creating a New Tablespace

For Oracle Database XE, you typically will not need to create new tablespaces unless your database size and user base grow enough to warrant upgrading to a different version of Oracle such as Oracle Database 10g Standard Edition. However, creating a new tablespace for a specific application is benefi- cial if you often migrate your application to a different database and want to use Oracle’s transportable tablespace feature to dramatically reduce the time it takes to move this data compared to traditional export/import techniques or copying tables or schemas over the network using a database connection. You may also want to create a new tablespace on a dedicated fast disk drive if you need above-average response time for the tables in this new tablespace; or, conversely, you may put the new tablespace on a slower disk drive if the required response time is not critical.
You create a new tablespace with the CREATE TABLESPACE command. In the following example, you create a tablespace specifically for your PHP applications that you will eventually migrate to other Oracle Database XE installations or an Oracle Database 10g Enterprise Edition installation:

create tablespace php_apps datafile 'D:\OracleXE\oradata\XE\php01.dbf' size 250m;

When you specify a datafile as storage for one of your tablespaces, you can place it anywhere with any name; however, using a name that is similar or identical to the tablespace name and a common suffix of dbf helps to identify the purpose of the operating system file when you are not connected to Oracle.
Querying the Oracle data dictionary (we cover data dictionary tables and dynamic performance
views in Chapter 34), you can see that your database now has four permanent tablespaces, one undo tablespace, and one temporary tablespace:

select tablespace_name, contents from dba_tablespaces;

TABLESPACE_NAME CONTENTS
------------------------------ --------- SYSTEM PERMANENT UNDO UNDO SYSAUX PERMANENT TEMP TEMPORARY USERS PERMANENT PHP_APPS PERMANENT

6 rows selected.

This information is also available via the Oracle Database XE administration pages in Home ➤
Administration ➤ Storage ➤ Tablespaces shown in Figure 30-1.

Figure 30-1. Querying tablespace names and allocated storage

Partitioning applications or groups of users into their own tablespace makes backup and recovery easier. You can take a single tablespace offline (except for the SYSTEM tablespace) and back it up or recover it while the rest of the database is online. We discuss tablespace recovery in Chapter 40.

Understanding Oracle Datatypes

Assigning the correct datatype to the columns in your database tables is another key to your appli- cation’s success—its reliability, scalability, and so forth. This includes not only making sure you define a numeric datatype to a column when you know that the column will only have numeric values, but also enforcing the column’s relationship to a column from the same domain in other tables.
In the following sections, we provide an overview of the Oracle built-in datatypes available in every edition of Oracle, including Oracle Database XE. In addition to Oracle’s built-in datatypes, we introduce the ANSI-supported datatypes to help ease the transition from datatypes you may be familiar with in other relational databases.

Built-in Datatypes

Oracle’s built-in datatypes include character, numeric, datetime, large object, ROWID, and long/raw. For the most part, you will use character, numeric, and datetime datatypes in your applications. If you have applications with high-resolution photos or video that you want to store in the database, you will most likely use large object (LOB) datatypes, either the built-in CLOB (character large object) or BLOB (binary large object) datatypes or Oracle-supplied datatypes such as ORDVideo, ORDAudio, and ORDImage.
You will rarely use ROWID datatypes in your applications; they are primarily used internally in indexes and for very specialized applications. Similarly, you will most likely not use long, raw, or long/raw datatypes, as they are included in Oracle Database XE for backward-compatibility with applications written in previous versions of Oracle.

Character Datatypes

You store alphanumeric data in character datatypes, using either the database character set or the Unicode character set. You create your database with a specified character set. For example, in the United States you may use the US7ASCII character set to support the English language and any other language that uses a subset of the English language. To expand your multilingual support in the database, you can create your database using the WE8ISO8859P1 character set, which supports English

and other European languages such as German, French, and Spanish. With the support for Unicode in PHP version 5.x, you can specify AL16UTF16 to support character sets from all known written languages in your Web applications.
If you use multilingual character sets, you must be aware of the storage consequences for character data; depending on the character being stored in the column, Oracle may require anywhere from 1 to
4 bytes to store a given character. Therefore, when you define the length of your character strings, you must be cognizant of whether you are using byte semantics or character semantics. Byte seman- tics assumes a single-byte character set, where one character always requires one byte of storage. On the other hand, character semantics permits you to define the lengths of your character strings as the number of characters, regardless of how many bytes each character requires. Byte semantics is the
default unless you override it using the initialization parameter NLS_LENGTH_SEMANTICS.

■Note Even if you do not define a character set at database creation that is sufficient to support your multilingual
needs, you can use the explicit Unicode datatypes: NCHAR, NVARCHAR, and NCLOB.

Table 30-1 lists the character datatypes and their qualifiers.

Table 30-1. Character Datatypes

Character Datatype Name Description

CHAR(size [BYTE|CHAR]) Fixed width character string that always stores size bytes when you specify BYTE, or characters if you specify CHAR, right-padded with spaces. Default size specification is BYTE unless you specify CHAR.

NCHAR(size) Same as CHAR but uses Unicode; size is always in characters.

VARCHAR2(size [BYTE|CHAR]) Variable width character string; stores only the number of bytes or characters assigned up to size bytes or characters. Default size specification is BYTE unless you specify CHAR. The datatype VARCHAR is equivalent to VARCHAR2, but Oracle reserves the right to change the definition of VARCHAR in future releases.

NVARCHAR2(size [BYTE|CHAR]) Same as VARCHAR2, but uses Unicode; size is always in characters.

LONG A deprecated character datatype included for backward- compatibility; Oracle may remove this datatype in future releases. Stores variable-length alphanumeric character strings up to 2GB in size. The CLOB datatype has all of the features of LONG and none of the restrictions (such as only one LONG column per table); therefore, Oracle strongly recommends using the CLOB datatype (discussed later in this chapter in the section “Large Object Datatypes”).

There are many situations where you should use NVARCHAR2 to support your application—for example, if you want to create your table once for all of your worldwide branch offices. In this example, you create a table to contain a list of customers whose name and country code are stored in the data- base character set. The address, city, and postal code can be any address in the world, and as a result you need to ensure that it is stored using the Unicode character set:

create table all_cust (cust_id number, cust_name varchar2(100), country_code char(3), address_line_1 nvarchar2(75), address_line_2 nvarchar2(75), address_line_3 nvarchar2(75), city nvarchar2(100), region nvarchar2(100), postal_code nvarchar2(25)
);

We will talk more about creating tables later in this chapter in the section “Creating and
Maintaining Tables.”

Numeric Datatypes
Numeric datatypes store positive and negative fixed-point and floating-point numbers, as well as a floating point representation for infinity and Not A Number (only for values imported from Oracle version 5). For all numeric datatypes, the maximum precision is 38 digits. The three numeric datatypes are NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. Table 30-2 lists the numeric datatypes and their qualifiers.

Table 30-2. Numeric Datatypes

Numeric Datatype Name Description

NUMBER[(precision[,scale])] Stores zero, positive, and negative numbers with a default precision of 38. The scale is the number of digits to the right of the decimal point and defaults to 0. The range for scale is
-84 to 127.

BINARY_FLOAT Stores single-precision floating-point numbers with 32 bits of precision.

BINARY_DOUBLE Stores double-precision floating-point numbers with 64 bits of precision; otherwise same as BINARY_FLOAT.

NUMBER Datatype

Numeric values stored in a NUMBER column are stored in scientific notation in variable-length format with up to 38 digits of precision. One byte of the internal representation is for the exponent; the mantissa uses up to 20 bytes with two digits stored in each byte. Therefore, the number 24632 uses more storage space than the number 120000000000.

■Note You can calculate the column size, in bytes, to store a numeric value in a column defined as NUMBER(p),
where p is the precision of a given numeric value, by using the formula ROUND((length(p)+s)/2))+1, where s
is 0 if the number is positive, and 1 if the number is negative.

If you know the values you expect to store in a NUMBER column, it’s a good idea to specify the precision and scale to enforce domain definitions and data integrity. For example, defining a column

to store gender as NUMBER(1) with a CHECK constraint (we explain CHECK constraints later in the section “Using Constraints”) prevents a programmer or data entry clerk from using this column to store a three-digit value for a country code along with the gender, for example.
Table 30-3 shows how the number 1,234,567.89 is stored internally for different NUMBER datatype
specifications.

Table 30-3. Numeric Datatype Rounding

Numeric Input Value NUMBER Definition Stored As
1,234,567.89 NUMBER 1234567.89
1,234,567.89 NUMBER(9) 1234567
1,234,567.89 NUMBER(6) Error condition, precision too big
1,234,567.89 NUMBER(9,1) 1234567.9
1,234,567.89 NUMBER(9,2) 1234567.89
1,234,567.89 NUMBER(7,-2) 1234500

Notice that no error occurs when Oracle rounds a number because of the scale, but Oracle will generate an error if you exceed the precision of the NUMBER definition.

BINARY_FLOAT and BINARY_DOUBLE

The BINARY_FLOAT and BINARY_DOUBLE datatypes support all functionality provided by NUMBER but use binary precision. This has both advantages and disadvantages; arithmetic calculations are typically faster and take less storage than NUMBER datatypes but cannot represent numbers such as 0.1 exactly. In practice, however, you will rarely encounter problems with rounding errors, such as obtaining a result of 0.09999999999 when you are expecting 0.1. Oracle’s floating-point numeric representation conforms to most of the IEEE Standard for Binary Floating-Point Arithmetic, IEEE 754. One typical use for BINARY_FLOAT and BINARY_DOUBLE is for statistical analysis where you will most likely need effi- cient handling of calculation-intense queries. In addition, you can save space in your tables if you have high-precision values. A BINARY_DOUBLE value will always take up 8 bytes of storage, whereas a NUMBER may use up to 21 bytes at the maximum precision.

Datetime Datatypes

Oracle Database XE stores dates as both point-in-time values (DATE and TIMESTAMP) and as periods of time (INTERVAL). The DATE datatype stores the four-digit year, month, day, hours, minutes, and seconds. The TIMESTAMP datatype expands the precision of date values to billionths of a second (0.000000001 second). For ease of use, Oracle defines a standard date format of DD-MON-YY so you don’t always have to use date conversion functions to convert date values in string constants to DATETIME values (we show you how to use date conversion and other functions in Chapter 35). So in this example, you do not have to explicitly convert the date string:

create table customer_comment ( customer_name varchar2(100), comment_date date, comment_text varchar2(500));

insert into customer_comment(customer_name, comment_date, comment_text)
values('Suzie Pustina','15-aug-06',
'Best service I''ve ever received from any technician.');

In this example, however, the date format in the string is not the default, so you must use the
TO_DATE conversion function:

insert into customer_comment(customer_name, comment_date, comment_text)
values('Ann Vandross',to_date('August 29, 2006','MONTH DD, YYYY'),
'Not sure if I will shop at your store again.');

Querying the table, you see that both dates are stored correctly in the table:

select customer_name, comment_date from customer_comment;

CUSTOMER_NAME COMMENT_DATE
---------------------- ------------ Suzie Pustina 15-AUG-06
Ann Vandross 29-AUG-06

■Note You can change the default date format in your database by changing the value of the initialization parameter
NLS_DATE_FORMAT.

Table 30-4 lists the datetime datatypes available in Oracle Database XE.

Table 30-4. Datetime Datatypes

Datetime Datatype Name Description

DATE Stores a date and time with a one-second precision. The date portion can be between January 1, 4712, BCE (Before Common Era) through December 31, 4712, CE (Common Era). If you do not specify a time, it defaults to midnight.

TIMESTAMP[(precision)] Stores date and time with subsecond precision, up to nine digits after the decimal point (one-billionth of a second). The date portion has the same range as DATE. The precision defaults to 6 and can range from 0 to 9.

TIMESTAMP[(precision)] WITH TIMEZONE

TIMESTAMP[(precision)] WITH LOCAL TIMEZONE

Same as TIMESTAMP but also stores a time zone offset. The
time zone offset defines the difference, in hours and minutes, between the local time zone and Coordinated Universal Time (UTC, also known as Greenwich Mean Time, or GMT). Two different columns defined as TIMESTAMP WITH TIMEZONE are considered equal if they represent the same absolute time. For example, 10:00 a.m. MST is equal to 11:00 a.m. CST.

Same as TIMESTAMP but when inserted into a table column it is converted from the local time to the database time zone. When the value is retrieved from the table column, the value is converted from the database time zone to the local time zone.

Table 30-4. Datetime Datatypes

Datetime Datatype Name Description

INTERVAL YEAR[(precision)] TO MONTH

INTERVAL DAY[(d_precision)] TO SECOND[(s_precision)]

Stores a period of time in years and months. The precision is the maximum number of digits required for the year portion of the time interval and defaults to 2. You use this datatype to store the difference between two datetime values if you require yearly or monthly granularity.

Stores a period of time in days, hours, minutes, and seconds. The value of d_precision is the maximum number of digits required for the day portion of the period; similarly, the value of s_precision is the maximum number of digits to the right of the decimal point required for the second portion of the period.

Large Object Datatypes

The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store and manipulate large blocks of unstructured data including but not limited to graphic images, video clips, sound files, and so forth. All LOB datatypes except for BFILE participate in transactions along with the rest of the row elements. This improves the overall integrity of your database but may increase the amount of space you have to allocate to your undo tablespace.
You can store LOBs three different ways: inline (within a table), out-of-line (outside of the table
in a different tablespace), or in an external file (using the BFILE type). Which method you use depends on a number of factors. For example, if you access a LOB column in your table infrequently, you should store the LOB in a different tablespace to maintain the performance of queries against the rest of the table. If you do not explicitly specify a tablespace for the LOBs in your table, Oracle stores them in the default tablespace for the user creating the table. Table 30-5 lists the LOB datatypes available
in Oracle Database XE.

Table 30-5. LOB Datatypes

LOB Datatype Name Description

CLOB Stores up to 8 terabytes (TB) of character data in the database using the database character set.

NCLOB Same as CLOB, except stores Unicode character data regardless of the database character set.

BLOB Stores binary unstructured data in the database up to 8TB per
BLOB column.

BFILE Stores binary variable-length data outside of the database using a pointer. The size of a BFILE is limited to a maximum of 8TB.

In this example, you create a table that stores your MP3 collection’s metadata in the USERS
tablespace and the MP3 files themselves in an existing tablespace called LOB_DATA:

create table my_mp3_files (
mp3_number number,
artist varchar2(100), song_title varchar2(150), album varchar2(150),
song blob) tablespace users lob(song) store as (tablespace lob_data);

ROWID Datatypes

ROWIDs are datatypes that store either physical or logical addresses of rows in an Oracle Database XE table. ROWIDs store physical addresses, and UROWIDs store both logical and physical addresses. For the vast majority of applications, using or viewing ROWIDs is not required to achieve the best performance. However, for specialized applications, you can retrieve a table row using a ROWID value with only one I/O operation. A ROWID is guaranteed to be unique across all tables in the database.
Every table in the database has a pseudo-column named ROWID; it is not part of the table’s structure nor does it take up any space. It merely shows you the physical address of the row’s block in the database, as in this query:

select rowid, customer_name from customer_comment;

ROWID CUSTOMER_NAME
------------------- ------------------------------- AAAEN8AAEAAAADdAAA Suzie Pustina
AAAEN8AAEAAAADdAAB Ann Vandross

Even though you can create additional physical columns in a table of type ROWID, there is no vali- dation to ensure that the value you place in the column is the address of a valid row in the database.

ANSI-Supported Datatypes

To improve compatibility with database applications that use ANSI SQL datatypes, Oracle supports ANSI datatypes such as CHARACTER, NATIONAL CHARACTER, and DECIMAL and stores them internally as compatible Oracle datatypes. Table 30-6 shows the ANSI SQL datatype and the equivalent Oracle datatype.

Table 30-6. ANSI to Oracle Datatype Equivalents

ANSI SQL Datatype Oracle Datatype
CHARACTER(n), CHAR(n) CHAR(n)

CHARACTER VARYING(n), CHAR VARYING(n) VARCHAR2(n), NATIONAL CHARACTER(n),
NATIONAL CHAR(n), NCHAR(n), NCHAR(n)

NATIONAL CHARACTER VARYING(n),
NATIONAL CHAR VARYING(n), NCHAR VARYING(n)

NVARCHAR2(n)

NUMERIC(p,s), DECIMAL(p,s) NUMBER(p,s) INTEGER, INT, SMALLINT NUMBER(38) FLOAT, DOUBLE PRECISION, REAL NUMBER

Creating and Maintaining Tables

Now that you know what kinds of datatypes you can put into a table, we need to show you how to create the table itself. In the following sections, we’ll also show you how to add validation rules to the columns in your table to enforce your organization’s business rules.
Earlier in this chapter, we reviewed the different datatypes available for columns and created some simple tables; now, we will show you how to combine those column datatypes along with table constraints (data validation rules), to ensure the data integrity of the information you insert into those tables.
When you create a table, you must specify the column names and datatypes for those columns;
you can, however, add or remove columns later with the ALTER TABLE command. Table and column names in Oracle Database XE must follow a few naming convention rules:

• Table names must be from 1 to 30 bytes in length.

• Table names must begin with a letter.

• Table names can include letters, numbers, and the symbols $, #, or _. Oracle discourages the use of $ or # in a table name because the resulting table name may conflict with a system table or view with the same name.
• Table names cannot be the same as reserved words such as NUMBER, INDEX, CREATE, and ORDER. (Imagine how many e-commerce Web sites are disappointed that they cannot use ORDER as a table name!)

Letters in table names are automatically uppercase; if you insist on including lowercase characters, other special characters, or reserved words in your table name, you can enclose the table name in double quotes, for example, "Order%This%Stuff!!!". The only restriction, as you might surmise, is that you cannot include a double quote in the table name. Use this convention with caution; every reference to a table with lowercase or special characters must always have double quotes around the table name.
In the following sections, we cover the basics of creating a table, validating the data you put into a table using table constraints, setting default values for columns in a table, and using shortcuts for creating new tables from existing tables. Finally, we cover the ways you can change table character- istics by renaming a table, modifying table columns, or dropping the table.

Creating a Table

To create a table you use the CREATE TABLE command. At a minimum, you must specify the column names and their datatypes. You can optionally specify default values and constraints. Alternatively, you can add these later. In this example, you want to create a table for the orders placed on your
e-commerce Web page powered by PHP, of course:

create table customer_order ( order_id number, customer_id number, order_date date, order_ship_date date, item_qty_num number, ship_notes varchar2(1000)
) tablespace php_apps;

Notice that we specify the tablespace PHP_APPS explicitly. It’s optional, and if you want all of your tables to reside in the default tablespace USERS, you can leave off the TABLESPACE clause. To see the structure of the table you have just created, you can use the DESCRIBE command either in the SQL

Commands Web interface or at the Run SQL Command Line prompt accessible from the start menu in Windows:

describe customer_order

Name Null? Type
-------------------------------- -------- ---------------------- ORDER_ID NUMBER
CUSTOMER_ID NUMBER ORDER_DATE DATE ORDER_SHIP_DATE DATE ITEM_QTY_NUM NUMBER SHIP_NOTES VARCHAR2(1000)

The column in the describe output labeled Null? has a value of NOT NULL for table columns that cannot contain NULL values. Most of the columns in this table should always be provided. We will show you how to require input values for specified columns, and many others, throughout the rest of this chapter.
Of course, you can also use the Oracle Database XE home page to view this table’s characteristics by navigating to the Object Browser from the Oracle Database XE home page. In Figure 30-2, you can see the structure of the table CUSTOMER_ORDER and the other operations you can perform on the table.

Figure 30-2. Viewing a table’s structure using the Object Browser

Using Constraints

Table or column constraints are one way to validate the data in a column or columns of a table. Oracle Database XE has five distinct types of constraints that you can define on a column or columns in a table: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY. Only the FOREIGN KEY constraint, as the name implies, does its validation in reference to another table within your database.
Constraints, like many other database objects, can be defined when the table is defined or added to the table later. You can also remove, temporarily disable, or reenable existing constraints. You can assign a name to a constraint when you create it; if you do not explicitly assign a name, Oracle will give the constraint a system-assigned name.
You can assign the NOT NULL constraint at the column level only. You can define all other constraints
at the column level or at the table level. Some constraints, such as a constraint that compares the values of two columns in the same table, must necessarily be defined at the table level.

NOT NULL

The NOT NULL constraint is the most straightforward of all the constraints. It specifies that a column will not allow NULL values, regardless of its datatype. In this example, you will drop and recreate the CUSTOMER_ORDER table to ensure that all columns must have values except for the ORDER_SHIP_DATE and SHIP_NOTES columns:
drop table customer_order;
create table customer_order (
order_id number not null, customer_id number not null, order_date date not null, order_ship_date date, item_qty_num number not null, ship_notes varchar2(1000)
);

Leaving the columns ORDER_SHIP_DATE and SHIP_NOTES as nullable makes sense: you won’t know
in advance what the ship date will be, and the order may not have any special notes or requests asso- ciated with it.
If the CUSTOMER_ORDER table already has data in it, you can alter the columns to add the NOT NULL
constraint. We show you how to modify the characteristics of an existing column later in this chapter in the section “Adding, Dropping, and Renaming Table Columns.”

CHECK

A CHECK constraint can apply to a specific column or it can apply at the table level if the constraint references multiple columns. CHECK constraints are useful if you need to keep values of a column within a certain range or within a list of specific values, such as ensuring that a gender column contains either M or F.
For our CUSTOMER_ORDER table, you want to ensure that when you enter or update an order manu- ally, the value for CUSTOMER_ID is a positive number; in addition, you want to ensure that the shipping date is not before the order date. Here is the CREATE TABLE command with a CHECK constraint for the ORDER_ID column and the table-level CHECK constraint for the DATE columns:

drop table customer_order;
create table customer_order (
order_id number not null unique,
customer_id number not null check(customer_id > 0), order_date date not null,
order_ship_date date, item_qty_num number not null, ship_notes varchar2(1000), check (order_ship_date >= order_date)
);

UNIQUE

You can apply the UNIQUE constraint at the column level or at the table level. It ensures that no two rows contain the same value for the column or columns that have the UNIQUE constraint. Oracle automatically enforces this constraint for a table’s primary key. You also use this constraint for a nonprimary key column that is a business key. The example in the previous section specifies the UNIQUE constraint at the column level; if you want to specify the constraint at the table level (i.e., you still only want one column to be unique but to specify the constraint outside of the column definition itself), you would use this CREATE TABLE command instead:

drop table customer_order;
create table customer_order (
order_id number not null unique,
customer_id number not null check(customer_id > 0), order_date date not null,
order_ship_date date,
item_qty_num number not null, ship_notes varchar2(1000), check (order_ship_date >= order_date),
constraint order_id_uk unique(order_id)
);

Whether you specify a particular constraint at the table level or at the column level is a matter
of style when the constraint only applies to one column; if a UNIQUE constraint applies to more than one column, you must specify it at the table level.

PRIMARY KEY

A PRIMARY KEY constraint is similar to a UNIQUE constraint, with two exceptions: a PRIMARY KEY constraint will not allow NULL values, and only one PRIMARY KEY constraint is allowed on a table. You can define a PRIMARY KEY constraint at either the column level or the table level. A PRIMARY KEY constraint is important when you want to find a way to uniquely reference a row in the table by storing the primary key in another table as a foreign key. We show you how to create a foreign key in the next section. For performance and data integrity, it is strongly recommended that every table have a primary key, even if it is a sequentially generated number; you can use an Oracle sequence to generate this number for you. We show you how to use sequences later in this chapter in the section “Creating and Using Sequences.” Here is the table definition for the CUSTOMER_ORDER table with a primary key of ORDER_ID:

drop table customer_order;
create table customer_order (
order_id number primary key,
customer_id number not null check(customer_id > 0), order_date date not null,
order_ship_date date, item_qty_num number not null, ship_notes varchar2(1000)
);

When you insert a new row into the CUSTOMER_ORDER table, duplicate values for the ORDER_ID
column are not permitted:

insert into customer_order
values (10001, 451106, sysdate, null, 10, '');

1 row created.

insert into customer_order
values (10002, 451400, sysdate, null, 4, '');

1 row created.

insert into customer_order
values (10001, 404118, sysdate, null, 7, '');

insert into customer_order
*
ERROR at line 1:
ORA-00001: unique constraint (RJB.SYS_C005266) violated

Since you already have an order row with an ORDER_ID of 10001, the third INSERT statement fails. Using the Object Browser from the Oracle Database XE home page you can see the contents of the table in Figure 30-3.
As you might expect, the Object Browser makes it easy to edit or query the contents of the table using a Web page.

Figure 30-3. Viewing a table’s contents using the Object Browser

FOREIGN KEY

A FOREIGN KEY constraint helps maintain the data integrity between a parent table and a child table. It allows you to define a column in the child table that exists as a primary key or a unique key in the parent table. When you enter a value into a column with a FOREIGN KEY constraint, Oracle checks the value against the primary key or unique value in the parent table to make sure it exists there; if it does not exist, the row cannot be inserted.
In this example, you create the ORDER_ITEM table (the child table) to hold the individual items for
a customer’s order in the CUSTOMER_ORDER table (the parent table):

create table order_item (
order_item_num number primary key, order_id number not null, item_cat_num number not null, size_code varchar2(6), color_code varchar2(4),
constraint order_item_fk foreign key (order_id)
references customer_order (order_id)
);

The CONSTRAINT clause enforces the relationship between the ORDER_ITEM table and the
CUSTOMER_ORDER table. When you insert a row into the ORDER_ITEM table, the value for the ORDER_ID column must exist in the list of values in the primary key column of the CUSTOMER_ORDER table; in this example, the column names have the same name, but this is not a requirement.

Setting Column Defaults

You can set default values for columns in your table. Many times a column may have the same value most of the time or for a given condition. This saves data entry effort and reduces errors if the value does not have to be entered for each row. For example, when you enter a customer’s order, the date of the order is usually the same as the date you enter the order. Therefore, you can set the default value for ORDER_DATE to SYSDATE; SYSDATE is the predefined system variable that contains the current date and time. In this example, you change the ORDER_DATE column in the CUSTOMER_ORDER table to default to the time you enter the order:

alter table customer_order modify (order_date default sysdate);

We show you how to alter the other characteristics of table columns in the section “Adding, Dropping, and Renaming Table Columns.”

Creating a Table Using a Query Against Another Table

In some situations, you may want to create another table that is identical to an existing table. For example, you may want to archive the old orders from the CUSTOMER_ORDER table to a table with the same structure with a different name. To do this, you can use a method called Create Table as Select (CTAS). All column names and attributes are inherited from the query using one or more base tables. In this example, you want to archive all orders from the CUSTOMER_ORDER table in August 2006 to a table called CUSTOMER_ORDER_AUG_2006:

create table customer_order_aug_2006 as select * from customer_order
where order_date between '1-aug-06' and '1-sep-06';

Table created.

describe customer_order_aug_2006

Name Null? Type
----------------------------------------- -------- --------------------- ORDER_ID NUMBER
CUSTOMER_ID NOT NULL NUMBER
ORDER_DATE NOT NULL DATE ORDER_SHIP_DATE DATE ITEM_QTY_NUM NOT NULL NUMBER SHIP_NOTES VARCHAR2(1000)

Modifying Table Characteristics

As with most database operations, you can perform the same operations with the command-line interface and with the Oracle Database XE Web interface. In the following sections, we show you how to rename a table, modify table columns, and drop tables using SQL commands. Not surprisingly, you will use the ALTER TABLE command to alter a table’s characteristics.

Renaming a Table

You use the rename clause of the ALTER TABLE command to change the name of a table. In this example, you decide that it is difficult for your users to use the Order%This%Stuff!!! table in their queries, so you change the name of the table to CUSTOMER_PROMOTIONS:

alter table "Order%This%Stuff!!!" rename to customer_promotions;

Table altered.

Adding, Dropping, and Renaming Table Columns

After your e-commerce Web site has been in production for a while, you realize that you need to make some changes to the CUSTOMER_ORDER table. First, you want to add a new column to track the last four digits of the credit card used for the order:

alter table customer_order add cc_suffix_txt char(4);

Table altered.

Next, you want to rename the ORDER_DATE, ORDER_SHIP_DATE, and SHIP_NOTES columns to conform with your company’s data element naming conventions:

alter table customer_order rename column order_date to order_dt;

Table altered.

alter table customer_order rename column order_ship_date to order_ship_dt;

Table altered.

alter table customer_order rename column ship_notes to ship_notes_txt;

Table altered.

Finally, you realize that you don’t need to store the total number of items in the order in the CUSTOMER_ORDER table since you can derive this information by counting the items in the ORDER_ITEM table:

alter table customer_order drop column item_qty_num;

Table altered.

Dropping a Table

Dropping a table deletes the table definition and its data. If there is room in the tablespace, you may be able to retrieve the table from the tablespace’s recycle bin. We show you how to use the recycle bin in Chapter 39. In this example, you realize that you don’t need the ALL_CUST table you created when you were testing your applications:

drop table all_cust;

Table dropped.

Creating and Maintaining Indexes

Indexes are optional data structures built on one or more columns of a table. The primary reason for creating an index on a column or columns in a table is to improve access to rows in the table. Instead of scanning all blocks in a table to find the desired row, you can access the index and read a small number of blocks from the index to find the address of the block in the table with the desired rows.
In the following sections, we show you how to create and maintain two types of indexes: B-tree and bitmap indexes.

Using B-tree Indexes
B-tree indexes are the default index type; they can be unique or nonunique. Oracle uses a unique B-tree index to enforce a PRIMARY KEY constraint. You use B-tree indexes for columns with medium to high cardinality—in other words, columns with more than just a few distinct values, such as last name, or city.
In this example, you realize that some of your queries on the CUSTOMER_ORDER table are taking a long time because your customer service representatives are searching for orders on the ORDER_DT column. Since this column is not indexed, any search based on this column must read the entire table contents until the desired row is retrieved. Therefore, you decide to create an index on the ORDER_DT column:
create index customer_order_ix01
on customer_order(order_date) tablespace php_apps;

Index created.

The name of the index must be unique within the schema that owns the index. After you create the index, your customer service representatives don’t notice much improvement in response time, so you decide to drop the index:

drop index customer_order_ix01;

Index dropped.

Using Bitmap Indexes

Bitmap indexes, in contrast to B-tree indexes, are useful in environments where your table data is relatively static and the indexed column has a relatively low cardinality, such as gender or state code.
You often analyze your CUSTOMER table by gender, so you create a bitmap index on the GENDER_CD
column:

create bitmap index customer_bix01 on customer(gender_cd);

create bitmap index customer_bix01 on customer(gender_cd)
*
ERROR at line 1:
ORA-00439: feature not enabled: Bit-mapped indexes

Bitmap indexes are one of the few features not available in Oracle Database XE; until you upgrade to another version of Oracle, you create a B-tree index instead:

create index customner_ix01 on customer(gender_cd);

Index created.

Creating and Using Sequences

Sequences are database objects owned by a schema that generate unique integers, positive or nega- tive. You typically use a sequence to assign unique primary keys for a table. An Oracle sequence is analogous to the AutoNumber datatype in Microsoft Access or the AUTO_INCREMENT column type in MySQL. As you might expect, you use the CREATE SEQUENCE command to create a sequence. Here is an example of a sequence starting with 1,001 and incrementing by 100:

create sequence new_cust_id_seq start with 1001 increment by 100;

Here are all of the options you can use with CREATE SEQUENCE:

START WITH: The initial value for the sequence.

INCREMENT BY: A positive or negative number added to the initial value to generate the next value in the sequence. The default is 1.

MINVALUE: The lowest value the sequence can generate. Defaults to -10E26 for ascending sequences and -1 for a descending sequence.

MAXVALUE: The highest value the sequence can generate. Defaults to 10e27 for ascending sequences and -1 for a descending sequence.

CACHE: The number of sequence values cached in memory. If the database is shut down and restarted, your sequence will still exist and generate unique values but may have gaps.

Sequences make it easy to create unique keys in INSERT or UPDATE statements. In this example, you decide to create a sequence for the CUSTOMER_ORDER table so your customer service representa- tives don’t have to manually insert the next available number for the ORDER_ID column, setting the starting value to 100,001 to ensure that you don’t reuse any existing order numbers:

create sequence customer_order_seq start with 100001;

Sequence created.

You use the NEXTVAL qualifier to retrieve the next value in the sequence. Accessing NEXTVAL again retrieves the next number in the sequence:

select customer_order_seq.nextval from dual;

NEXTVAL
----------
100001

select customer_order_seq.nextval from dual;

NEXTVAL
----------
100002

■Note The DUAL table is a system table available to all database users. It has one row and one column and is
useful when your query does not need to access a table, such as when you want to access a sequence or perform a calculation that does not require data from a table.

If you want to use the same sequence value in two different INSERT or UPDATE statements, you can use CURRVAL instead:

select customer_order_seq.currval from dual;

CURRVAL
----------
100002

select customer_order_seq.currval from dual;

CURRVAL
----------
100002

The next time you insert a row into the CUSTOMER_ORDER table, you can use the new sequence:

insert into customer_order values
(customer_order_seq.nextval, 402169, sysdate, null, '', '4053');

1 row created.

Summary

As a DBA, you need to know how to create and manage tablespaces to efficiently use your database’s disk space, even if you only have two or three tablespaces in addition to the tablespaces included with an installation of Oracle Database XE.
Knowing the right datatypes to use for your table columns will make your application’s tables easier to use as well as ensure the integrity and quality of the data you put into the table. Many of the validation rules you might put into your PHP application for other databases can be enforced in Oracle Database XE.
Now that we have given you the tools to create the tables and columns for your application, we’ll
focus on security in the next chapter, to make sure that only authorized users can access your table’s data. We’ll show you how to create and manage user accounts as well as show you how to audit access to the objects in your database.