Page 119
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
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