Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Primary Keys

In Chapter 1, we briefly discussed normalizationand 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 keyfor 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 keyconstraint 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.

SQL> alter table author
  2>   add (constraint author_pk primary key (author_key));

Table altered. 

SQL> desc author
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------- AUTHOR_KEY                                NOT NULL VARCHAR2(11)
 AUTHOR_LAST_NAME                                   VARCHAR2(40)
 AUTHOR_FIRST_NAME                                  VARCHAR2(20)
 AUTHOR_PHONE                                       VARCHAR2(12)
 AUTHOR_STREET                                      VARCHAR2(40)
 AUTHOR_CITY                                        VARCHAR2(20)
 AUTHOR_STATE                                       VARCHAR2(2)
 AUTHOR_ZIP                                         VARCHAR2(5)
 AUTHOR_CONTRACT_NBR                                NUMBER(5) 

Notice that my author key is now listed with a NOT NULL constraint.  That is part of being a primary key.  In fact, a primary key constraint insures that the column (or columns) are not NULL and are unique.  Not allowing NULLs ensures that each row has an author key.  Being unique ensures that no two rows have the same author key.  As we will learn when we discuss unique constraints, uniqueness is enforced using an index.  If we check for indexes on the author table, we will now find one.

SQL> select
  2    index_name,
  3    table_name
  4  from user_indexes; 

INDEX_NAME                     TABLE_NAME
------------------------------ ---------------------------AUTHOR_PK                      AUTHOR
 

SQL> select
  2    constraint_name,
  3    constraint_type,
  4    table_name
  5  from user_constraints;

CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - --------------------------AUTHOR_PK                      P AUTHOR

We now have two additional objects in the database, an index named author_pk and a constraint named author_pk.  Primary keys are normally defined, then you create the table.

SQL> create table editor
  2  (
  3    editor_key               varchar2(9) not null,
  4    editor_last_name         varchar2(40),
  5    editor_first_name        varchar2(30),
  6    editor_hire_date         date,
  7    editor_active            char(1) 
  8      constraint active_ck check (editor_active in ('Y','N')),
  9    constraint editor_pk primary key (editor_key)
 10      using index tablespace indx
 11  ); 

Table created.

Here, I created a table called EDITOR with a primary keyon the editor_key column.  The index used to enforce the primary key will be built in the indx tablespace.  If the “using index tablespace indx” part is not present, the database creates the index in the user’s default tablespace.  The primary key is tied directly to the table it is built on.  If I drop the table, the constraint is also dropped.  I can drop the primary key, however, without dropping the table.

SQL> alter table editor drop primary key; 

Table altered. 

SQL> alter table author disable primary key;

Table altered.

SQL> alter table author enable primary key;

Table altered. 

In the example above, we dropped the primary keyfrom the EDITOR table.  The table remains but the constraint is removed from the database.  If we want to have the primary key back, we must recreate it.  In the second statement, we disabled the primary key.  The key remains in the database but is no longer applied to INSERTSs and UPDATEs.  In the last statement, we enabled the key.  Dropping or disabling a primary key will cause the database to drop the enforcing index.

As we have seen, the primary keyconstraint enforces uniqueness using an index.  This is the same method used by the unique constraint.

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.