2 Nick-SQL : а откуда сомнения появились?

Может поможет?

Oracle 9i:
The Complete reference

kevin Loney
George Koch

McGraw-Hill /Osborne
ISBN 0-07-222521-1


p 381

Enforcing Uniqueness
Recall from Chapter 2 that a set of tables is said to be in Third Normal Form if all of the columns
in each table ’s rows are dependent only on the primary key.In the BOOKSHELF_AUTHOR
table,the primary key is the combination of the columns Title and AuthorName.In other tables,
a primary key might be an employee ID,a client ID,an account number,or,in a bank,a
combination of branch number and account number.
In each of these cases,the uniqueness of the primary key is critical.A bank with duplicate
account numbers,or a billing system with duplicate client IDs,would wreak havoc as
transactions were posted to accounts belonging to different people but having the same primary
key (this is why names usually are not commonly used as primary keys —there are too many
duplicates).To avoid this danger,have your database help prevent the creation of duplicate
primary keys.Oracle offers two facilities that help:
You can guarantee the uniqueness of a key through either indexing or constraints.
You can use the sequence generators (discussed later in this chapter).
Creating a Unique Index
You can create a unique index on the combination of Title and AuthorName in the BOOKSHELF_
AUTHOR table in three ways:by creating a primary key constraint,by creating a unique constraint,
or by creating a unique index.If you create a constraint,you will be able to create foreign keys that
refer to it.If you create the unique index first,you will still be able to create a primary key on the
table —Oracle will use the existing index as the primary key index.
The following listing shows the create index command for this multicolumn index:
create unique index BA$TITLE_AUTHOR
on BOOKSHELF_AUTHOR(Title, AuthorName);
The primary key method is shown here:
alter table BOOKSHELF_AUTHOR
add constraint BA_PK primary key (Title, AuthorName);
To create a unique constraint,just replace the primary key clause with unique .When you
attempt to create a unique index on a table that already has data in it,the command will fail if
any duplicates exist.If the create unique index statement succeeds,then any future attempt to
insert (or update )a row that would create a duplicate key will fail and result in this error message:
ERROR at line 1: ORA-00001: unique constraint (BOOKSHELF_AUTHOR.BA_PK) violated
When you create an index,it requires storage space.See “Placing an Index in the Database,”
later in this chapter,for details concerning the location of the created indexes.