Previous | Table of Contents | Next

Page 119

CHAPTER 6

Defining the Database
Objects

This chapter switches the emphasis from selecting data from the database to defining the tables and objects in the database. More specifically, this chapter covers techniques for creating, altering, and deleting tables, indexes, synonyms, and views. This is done in SQL*PLUS by using Data Definition Language (DDL) commands. This chapter also discusses table constraints, which are important devices for maintaining the integrity of the database. Finally, this chapter covers the commands to create user ids and tablespaces. After reading the chapter, you should have an understanding of the commands needed to create a database. The installment at the end of the chapter will give you an opportunity to create an actual database.n

Page 120

Defining the Table Items

Oracle tables consist of one or more columns or fields. A column is a named cell within a record that contains a value. The columns are defined with a format or characteristic that allows for their most efficient use. Columns generally have character or numeric formats. The initial definition occurs in the create table command. This command consists of a series of column or field names and their data format. It is important to define these columns with the proper format. The format of the items determines their ease of use. It is very difficult, for instance, to sort records based on a column defined as character but populated with numeric characters. A character value of `9' has a higher sort value than a character value of `11.' This means that as you sort the column, the `11' appears before the `9' because character columns are sorted by position. Changing the `9' to `09' will change the sort order. This new value will have a lower sort order since a `0' is less than a `1.' If the developer wants the values to sort properly without having to enter the `0,' a numeric format should be used. Figure 6.1 illustrates the differences in sorting numeric and character values.

FIG. 6.1
Sorting alphanumeric
versus numeric
columns.


Columns defined numerically are evaluated in their entirety. The preceding sorting problem does not occur. With numeric columns, your concern is the size of the value and its precision. Precision is the number of decimal positions. Columns defined with one decimal position cannot contain a value with three positions. Columns that contain dates should be defined as a date. DATE is a special numeric format that stores the value as a number but displays the value in an alphanumeric format. The default format is `DD-MON-YY' as in `08-APR-51.'

Page 121

It is important to try to define the column formats correctly when the tables are created. If you need to change them, however, Oracle enables you to do it. Be aware, however, that special problems occur when the columns contain values and the format is changed to numeric or is made smaller. The section on the alter table command discusses this in more detail.

Table 6.1 contains the valid column formats.

Table 6.1 Valid Data Types

Data Type Description
char(n) Defines a fixed length alphanumeric column of length
(n). The maximum size is 255 characters. The default is 1 byte.
date Defines a date format. It accepts valid dates from January 1, 4712 BC
to December 31, 4712 AD.
decimal Same as the NUMBER data type. It does not accept size or decimal
digits as an argument.
float Same as the NUMBER data type.
integer Same as the NUMBER data type. It defines the value as a whole
number and does not accept decimal digits as an argument.
integer(size) Specifies an integer data type of the length specified in the size value.
long Defines a character data type up to 65,535 characters. Only one
LONG column may be defined per table. This type of column may not be used
in subqueries, functions, expressions, where clauses, or indexes.
long raw The same as LONG, except it contains raw binary data.
long varchar The same data type as LONG.
mlslabel Four-byte representation of a secure operating system label.
number Defines a numeric data type with space for 40 digits, and a space for a
sign and a decimal point. The numbers may be expressed in two manners:
with numbers from 0 to 9, the signs + and -, and a decimal point; and in
scientific notation (1.951E4 as 19510).
number(size) Defines a number column that contains the number of digits equal to
the value in size. The maximum number of digits is 105.
number(size,d) Defines a number column that contains the number of digits equal to
the value in size and contains the number of decimal position specified in
d. A format specification of number(3,2) cannot contain a number greater
than 9.99.
number(*) The same data type as number.
smallint The same data type as number.
                                     		continues

Previous | Table of Contents | Next