 |
|
ss
Oracle Tips by Burleson |
Database Objects
Everything in an Oracle database is an
object, from tables to queries. In the last chapter, we introduced
you to creating and managing tables; however, there are many other
objects in the database. In this chapter, we are going to extend
your knowledge of tables by integrating constraints. Constraints
protect your data and include primary and foreign keys. They
enforce uniqueness and check data before inserting it into a table.
We will then discuss views and materialized views, and how they
differ from tables and when they are used. Next, we will show you
how to use indexes to make your queries more efficient.
All of these objects play an important part
in protecting your data, while enhancing the database’s efficiency.
Let’s start our discussion in the last chapter on tables, with some
details on integrity constraints.
Integrity Constraints
Constraints protect the integrity of your
data. Most DBAs look at constraints as rules attached to a table;
however, they are separate objects within the database. They are
applied whenever data is added or changed in a table or when the
constraint is enabled. Since they are separate objects, they can be
disabled or enabled individually. When a constraint is disabled, it
remains in the database but is not used to validate data, allowing
possibly invalid data to be placed in the table. Some constraints
are used to insure that the tables can be related on keys.
Primary Keys
In Chapter 1, we briefly discussed
normalization and schema design. One tenet of a normalized design
was that each table would have a key and that all of the data in
that table would be dependent only on that key. That key is called
the primary key for that table. Each table can have only one
primary key; however, that key may consist of more than one column.
If we look at the AUTHOR table, the author_key is the primary key as
all the other data in the row relates to only one author key.
I can have two authors named Sam Smith, but
I can only have one author key A101. My two Sam Smith authors would
each have a unique author key, which would distinguish between
them. Although we have been using the author key to join the AUTHOR
table to other tables, we do not have a primary key constraint on
the table. I can enter a row with a duplicate key, so my data is
not protected. Most primary keys are created as part of the CREATE
TABLE command. But, I can modify my table to add the primary key if
the table already exists.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
