021-024

Previous

Table of Contents

Next

Chapter 2
The Architecture Of The Oracle Database, SQL, And PL/SQL

The Oracle database is not a simple piece of software. Fortunately, becoming a good PL/SQL developer doesn t require that you know everything about the internal structures of the database. Unfortunately, becoming a good PL/SQL developer requires that you understand a large part of the database architecture. This chapter is intended to provide you with a very high level perspective on what you need to know.

If you are already experienced with Oracle, SQL, and PL/SQL, you ll probably want to browse this chapter and move on to Chapter 3. If you re completely new to Oracle (either with or without previous development experience), make sure that you read this chapter before proceeding.

The Architecture Of The Oracle Database

Your knowledge of the database needs to cover the following basics:

   Constraints

   Indexing

   Locking

   Roles and privileges

   Schemas

   Sequences

   SGA (system global area)

   Snapshots

   Synonyms

   Tables and columns

   Views

This list is by no means comprehensive. Your work as an application developer using Oracle and PL/SQL will continually expose you to new challenges; with hard work and dedication, you ll meet those challenges head on.

Constraints

A constraint is a condition placed on a table, typically to satisfy a business rule. When a constraint is placed upon a table, every row in the table must satisfy that constraint. Listing 2.1 provides a definition of the table STUDENTS.

Listing 2.1 A sample table creation script using constraints.

 CREATE TABLE STUDENTS AS (ssn                    NOT NULL   number(9),  first_name             NOT NULL   varchar2 (10),  last_name              NOT NULL   varchar2 (12),  middle_name                       varchar2 (10),  street_address         NOT NULL   varchar2 (30),  apartment_number                  varchar2 (4),  city                   NOT NULL   varchar2 (30),  state_code             NOT NULL   varchar2 (2),  zip_code               NOT NULL   number (5),  home_phone             NOT NULL   number (10),  degree_plan                       varchar2 (20),  overall_gpa                       number (3, 2),  most_recent_gpa                   number (3, 2)); 

Notice the columns ssn , first_name , last_name , street_address , city , state_code , and zip_code have the NOT NULL constraint. This constraint requires that each row of data in the STUDENTS table has values for these columns.

How Are Constraints Used?

As previously stated, constraints are often used to enforce business rules. A business rule that all students have a home address, name , and social security number is enforced through the use of the NOT NULL constraint. Several types of constraints exist, including:

    NOT NULL ”A NOT NULL constraint requires that a column contains a value in all rows of a table. If no value is specified for a column with this constraint and no other database functions or objects affect the data before it is added to the table, an error will occur.

    default ”Whenever a NULL value is inserted for the column, it is replaced with the value of the expression specified in the default constraint. For instance, if an hourly salary column is left NULL , it would default to the value of the minimum wage.

    check ”A check constraint allows the Database Administrator (DBA) to specify an expression, which the value in the column must satisfy. If the column does not satisfy the expression, an error will occur. The check constraint is not extremely powerful, but does provide a handy way to enforce simple conditions without using code.

    unique ”A unique constraint specifies that the value of the particular column is unique to a single row inside the table. For instance, in the STUDENTS table, the column ssn would be unique for every student. If you attempt to add a duplicate social security number to the table, an error will occur.

    primary key ”A primary key constraint specifies that the column is part of the table s primary key. A primary key makes every row within the table unique. A table has only one primary key, which is composed of all the columns that have the primary key constraint. To specify that the value for an individual column must be unique in a table with more than one unique column in the primary key, the unique constraint must be used. Each element of the table s primary key is, by definition, NOT NULL .

    foreign key ”A foreign key constraint specifies that a value for the specified column(s) must exist as primary key values in another table. For instance, the column degree_plan in the STUDENTS table might have a foreign key reference to the DEGREE_PLANS table s degree_plan column (a primary key column for the DEGREE_PLANS table). If this is the case, no student can have a degree plan that does not exist in the DEGREE_PLANS table.

The use of constraints to enforce business rules does have limitations. Some business rules are simply too complex to enforce with the limited functionality of the check constraint; in these situations, the system often has to rely on the use of database triggers (which use PL/SQL and can handle complex logic).

A revised description of the STUDENTS table might appear as shown in Listing 2.2.

Previous

Table of Contents

Next