Object Datatypes

Object-oriented extensions are discussed in more detail in Chapter 15, “Object-Oriented Extensions in Oracle8.” However, I wanted to provide an introduction to this new feature of Oracle8 in this chapter so that you get the complete picture of the data storage tools that are available to you. You have the option of nesting tables within objects to handle cases where there are multiple values of a given attribute associated with a single instance of an object. To create a user-defined object datatype, you would issue a command similar to:

Create type address as object

     (street     varchar2(25),

     city        varchar2(25),

     state       varchar2(2));

To make use of this object (that is, create a table that contains that datatype), you would issue a command similar to the following:

Create table people

     (employee_id     number(10),

     location         address); 

To access a value within the object (here, the people table to which you have assigned an alias of employee), you would issue a command similar to:

Select employee.location.street from people employee;

If this notation seems confusing to you, you have probably not yet been exposed to object-oriented design philosophy and programming tools. Remember, Oracle8 is a fully functional relational database management system. If you find these object-oriented extensions confusing and you do not see a need for them, you can stick to the traditional tables, views, and standard Oracle datatypes.

Constraints

Constraints help the database designer ensure that only “good” data will be placed into the various tables by developers and end users. The following are the constraint types that you will run across in Oracle8:

•  null. This indicates that the only valid value for the column on which this constraint is applied is null (nothing can be inserted into this column).

•  not null. This indicates that a value must be specified for this column.

•  default. This specifies a default value for a given column. If the insert statement does not supply a value for this column, the default value is automatically used by Oracle.

•  unique. This specifies that no two rows will have the same value for that column (except if the column is null, in which case there can be duplicates).

•  PRIMARY KEY. This specifies that a unique, non-null index will be created for this column or columns. Each row will have a different value which can be used as an absolute reference to that row.

•  FOREIGN KEY. This specifies that the value for this column (or columns) must either be null or match a corresponding column (or set of columns) in a table specified as part of the foreign key. This is a good way to implement a list of valid values that is stored in a table in the database and therefore is relatively easy to update as business needs change.

•  List of values. This specifies that the column contains a value that is in a fixed list specified at the time the table is created. This is acceptable for things that, by their very nature, have only a limited set of values (for example, yes/no, true/false). You can check that the value meets certain logical criteria related to one or more columns in the table. I would recommend using a foreign key and valid values table if there is any chance that the list of acceptable values for a column will change.

There are two places that a constraint can be applied. The first is on an individual column. This constraint applies to the column individually. The second constraint is at the table level and enables you to work with one or more columns. The following examples show a unique constraint applied at the column and table levels:

SQL> create table locations

  2  (location_id number(5),

  3  location_name varchar2(30) constraint c_location_name unique,

  4  location_state varchar2(2),

  5  active_flag varchar2(1) check (active_flag in (‘Y’,’N’)));



Table created.

SQL> create table locations2

  2  (location_id number(5),

  3  location_name varchar2(30),

  4  location_state varchar2(2),

  5  active_flag varchar2(1),

  6  constraint c_location_name2 unique(location_name),

  7  check (active_flag in (‘Y’,’N’)));



Table created. 

Summary

This chapter provided an introduction to the database objects associated with Oracle. These objects store the information and provide resources (sequences) which enable you to build applications based on Oracle8. All Oracle developers do not need some of these resources (for example, user-defined object datatypes). However, you can think of these resources as a tool kit from which you can choose the tools you need to get a particular job done.