Creation of Relational Tables

Tables are owned by users. You may hear users referred to as schemas and see this term used in Oracle documentation, but as with previous Oracle versions, whether you call them schemas or users makes little difference'the two terms are synonymous. For a given application it is suggested that its tables reside in a single dedicated tablespace or group of tablespaces. This leads to the corollary that all of an application's tables should be owned by a single user, or if you prefer, reside in a single schema. This makes further maintenance, such as exports, imports, synonym creation, table creates, and drops, easier to deal with.

Like the other database objects, tables are built using the CREATE command. This command has changed significantly since early versions of Oracle7. In fact, I can probably say without fear of contradiction that it, and the ALTER TABLE commands, are the most complex, convoluted commands in Oracle8i. (For those interested in the command syntax, refer to the Oracle documentation available on the technet.oracle.com Web site or through links on the Wiley Web site.)

Oracle9i offers a cornucopia of new table options. Oracle9i includes the ability to partition LOBs and types, and to subpartition partitions, as well to specify hash, range, and list partitioning. We will cover the use of types (in the creation of nested tables and varrays) in  Chapter 5. Here, we will begin by looking at table-naming conventions,  then we will  examine the creation of standard tables, standard tables with LOBs, index-only tables (IOT), and partition tables under Oracle9i. Then we will move on to examine the use of the ALTER command, as well as the DROP and TRUNCATE commands with Oracle9i tables. As a final topic in this chapter, we will cover the use of external tables in Oracle9i.

Table-Naming Convention: An Example

Before a table-naming convention is established for a given project, the list of allowed abbreviations and acronyms should be compiled.  This will  prevent, for example developer A from naming the accounts payable table act_pay and developer B from naming the accounts receivable table accnt_rec.

According to the relational model, table names should always be a plural noun, since they are collections of entities (it logically follows that entity names should be singular in data models). Names should be as short as possible, between 8 and 16 characters. This length restriction is important, because, for column foreign key definitions and constraint naming, the table name is used in conjunction with column names in Designer and Developer products from Oracle.

For example, the table used to track ACCOUNTS PAYABLE in the PITS (Procurement Information Tracking System) application would be: 

ACCT_PAY

assuming the standard abbreviation for ACCOUNT is ACCT and PAYABLE is PAY.

Wherever possible, abbreviations and acronyms should be used to shorten the table name. The TAB_COMMENTS and TAB_COL_COMMENTS tables can be used to store a more detailed name, which can be used in a simple help stored procedure to provide detailed information on the table and its columns. The COMMENT command is used to add comments to the DBA_TAB_COMMENTS and DBA_COL_COMMENTS views; it has the general format:

COMMENT ON TABLE|COLUMN [schema.]table|view|snapshot[.column] IS 'text';

The importance of short table names cannot be stressed enough. However, using codes like A1, B1, and Z28 for your tables is also to be avoided. Short meaningful names are the best.

A specialized type of table is the intersection table. An intersection table allows resolution of many-to-many and recursive relationships. Let's say we have a  many-to-many relationship between the PARTS table and the VENDORS table (VENDS for short). The resolving intersection table should be named:

VENDS_PARTS

(assuming the VENDORS table is the driving table for the relationship).

Creation of Simple Relational Tables: An Example

I define simple relational tables as those using standard relational structure, without the use of user-defined types (UDT), partitions, or special storage options such as index-only structure. In many applications the DBA works with, these will be the majority of tables involved.

When creating simple relational tables, the CREATE TABLE command is used, with constraint clauses, simple physical attribute, and a STORAGE clause. The generalized format for the command is:

Let's look at a very simple table creation command:

CREATE TABLE test_dba.test1
(test_col1 NUMBER,
 test_col2 VARCHAR2(10));

In this example we  created a table called test1 in the schema test_dba. The table test1 would be created in the default tablespace of the specified schema/user owning the table, not the default tablespace of the creating schema/user. The table test1 would inherit the storage and physical attributes of the default tablespace of test_dba.

So why is it good or bad to use such a simple command as illustrated here? Actually, it depends on how you have defined the default storage characteristics of the default tablespace and whether or not you want the table created in the default tablespace. If the creation of test1 using the default storage and physical attributes, as well as the default tablespace, is what you desired, then this command is what to use. If, on the other hand, you did not want any of this to occur, then you used too simple a command. Let's look at another ?problem? command:

CREATE TABLE test_dba.test1
(test_col1 NUMBER PRIMARY KEY,
 test_col2 VARCHAR2(10));

This command has the same problems as did the first table creation command we examined. Moreover, there is an added twist: the PRIMARY KEY column constraint will result in the creation of an index, and that index will be stored in the default tablespace for test_dba with the default storage and physical attributes and a system-generated name such as: SYS_C001967. The creation of both the table and its index in the same tablespace results in instant contention for resources between the index and table, hence subsequent performance problems. An additional issue is that both the UNIQUE constraint specification and the PRIMARY KEY specification result in the creation of an index and an additional NOT NULL constraint against the specified column. So how should we specify table test1 to avoid all of these problems?

CREATE TABLE test_dba.test1
(test_col1 NUMBER,
 test_col2 VARCHAR2(10),
CONSTRAINT test1_pk PRIMARY KEY(test_col1)
USING INDEX TABLESPACE test_indexes
PCTFREE 30

Why is this longer format for the CREATE TABLE command better? Let's examine the command and see.

* First, we have isolated the column definitions so that they aren't obscured by layers of added constraint material.

* Next, the constraint is clearly defined after the column definitions with its own storage, physical attributes, and tablespace definition.

* The constraint is named so that, instead of a constraint named SYS_C001967, we have one clearly identified as to table and type of constraint: test1_pk. Note that the automatically defined index will also be named test1_pk, allowing ease of constraint, table, and index tracking.

* Finally, by specifying the tablespaces for both the index generated by the primary key constraint and the table itself, we have assured the physical separation of the index and table, thus eliminating contention for resources.

Notice that we also used the new Oracle8 feature that allows us to specify the buffer pool to which the table blocks should be loaded. In this case, we specified the KEEP pool, meaning we want the tables blocks to be cached in the KEEP section of the db buffer pool. (See Chapter 13, Database Internals Tuning, to learn how to use multiple buffer pools,.) The other options for the BUFFER_POOL clause are RECYCLE and DEFAULT, with DEFAULT correlating to the old behavior in pre-Oracle8 databases.

A slightly more complex example involves the use of foreign keys. As the name implies, a foreign key references a table outside of the current table. A foreign key must reference a primary or unique key value already in existence in the other table. Building on the preceding examples, look at the definition of our table test1. Notice that in test1 we have specified the primary key column test_col1; let's create a table test2 that has a primary key and a foreign key that references using a foreign key back into table test1.

CREATE TABLE test_dba.test2
(test2_col1 NUMBER,
 test2_col2 NUMBER,
 test2_col3 DATE,
CONSTRAINT test2_pk PRIMARY KEY (test2_col1,test2_col2)
USING INDEX TABLESPACE test_index
PCTFREE 30
INITRANS 5
MAXTRANS 255
STORAGE(INITIAL 100K NEXT 100K
 MINEXTENTS 1 MAXEXTENTS UNLIMITED
PCTINCREASE 0),
CONSTRAINT test2_fk1 FOREIGN KEY(test2_col2)
 REFERENCES test_dba.test1(test_col1))
PCTFREE 10
PCTUSED 60
INITRANS 5
MAXTRANS 255
STORAGE(INITIAL 1M NEXT 1M
 MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0
 BUFFER_POOL KEEP)
TABLESPACE test_data
/

The table test2 is created with a concatenated primary key called test2_pk, which comprises the columns test2_col1 and test2_col2. The column test2_col2 is also a foreign key reference back to our original table test1. What a foreign key implies is that for every value entered into test2.test2.col2 ,there must be a primary key value in test1.test.col1 that matches it exactly. This is the basis for the concept of referential integrity in relational databases.

What other types of constraints can we define? We haven't looked at unique value, check, and default value constraints yet. A UNIQUE constraint is nearly identical to a primary key constraint in that it enforces that each value in the unique column be unique and uses a unique index to enforce this constraint. However, in a unique column, NULL values are allowed, while they are forbidden in a primary key. Look at Listing 4.1.

SQL> desc test_dba.test3
 Name                            Null?    Type
 ------------------------        -----    ------
 TEST3_COL1                      NOT NULL NUMBER
 TEST3_COL2                      NOT NULL NUMBER
 TEST3_COL3                               DATE

Listing 4.1  Example of a table creation script.   

Notice here in the describe of the table test3 that the column test3_col3 is not specified as a NOT NULL even though it is defined as a uniquely constrained value. The last two constraint types are check and default value. The NOT NULL constraint is a specialized form of the check constraint, in that it checks that the value entered into a column is not null; that is, it must have a valid value. More complex versions of the check constraint can be used to enforce ranges of values for a specific column. Look at Listing 4.2.

SQL> CREATE TABLE test_dba.test4
  2  (test4_col1 NUMBER,
  3   test4_col2 NUMBER,
  4   test4_col3 DATE CONSTRAINT test4_ck1
  5    CHECK(test4_col3 BETWEEN
  6 TO_DATE('01-jan-1999 00:00:00','dd-mon-yyyy hh24:mi:ss') AND
  7 TO_DATE('01-jan-2000 00:00:00','dd-mon-yyyy hh24:mi:ss')),
  8  CONSTRAINT test4_pk PRIMARY KEY (test4_col1,test4_col2)
  9  USING INDEX TABLESPACE test_index
 10  PCTFREE 30
 11  INITRANS 5
 12  MAXTRANS 255
 13  STORAGE(INITIAL 100K NEXT 100K
 14  MINEXTENTS 1 MAXEXTENTS UNLIMITED
 15  PCTINCREASE 0),
 16  CONSTRAINT test4_fk1 FOREIGN KEY(test4_col2)
 17  REFERENCES test_dba.test1(test_col1)
 18  ON DELETE CASCADE,
 19  CONSTRAINT test4_uv1 UNIQUE (test4_col3)
 20  USING INDEX TABLESPACE test_index
 21  PCTFREE 30
 22  INITRANS 5
 23  MAXTRANS 255
 24  STORAGE(INITIAL 100K NEXT 100K
 25  MINEXTENTS 1 MAXEXTENTS UNLIMITED
 26  PCTINCREASE 0))
 27  PCTFREE 10
 28  PCTUSED 60
 29  INITRANS 5
 30  MAXTRANS 255
 31  STORAGE(INITIAL 1M NEXT 1M
 32  MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)
 33* TABLESPACE test_data
SQL> /

Table created.

Listing 4.2  Second CREATE TABLE example.

Notice here  that the check constraint is a column-level constraint and can't be placed at the table level. The check constraint on the column test4_col3 restricts the dates placed into the field to be between 01-Jan-1999 00:00:00 and 01-Jan-2000 00:00:00.

Also notice in Listing 4.2 the inclusion of the ON DELETE CASCADE clause to the foreign key defined on test4_col2. This clause forces any values in this row to be deleted when the row from the source column in test1.test_col1 is deleted. Note that there is no similar clause to allow updates to be carried forward from the parent table to the child table.

Providing Cascade Update Functionality

A common situation is that a master table may be updated, leaving dependent tables without a link back to the master?in which case, a cascade update option would be a nice one to have. Unfortunately, Oracle Corporation doesn't provide this as a native capability, so a PL/SQL option must be developed.